none
Why use FOR XML PATH RRS feed

  • Question

  • Why would you use FOR XML PATH? I understand to extract the data and produce the result set as XML...but for what purpose? Loading other databases? Passing the data? Does FOR XML PATH produce a result set that is less voluminous than say an Excel spreadsheet? 
    Tuesday, September 17, 2019 10:15 PM

All replies

  • Hi ITBobby,

    An excerpt from: Extensible Markup Language (XML)

    "...Extensible Markup Language (XML) is a simple, very flexible text format derived from SGML (ISO 8879). Originally designed to meet the challenges of large-scale electronic publishing, XML is also playing an increasingly important role in the exchange of a wide variety of data on the Web and elsewhere..."

    XML is very useful for data feeds exchanges between loosely coupled systems. Though many still use archaic *.csv files for it today. And *.csv files are problematic to process because delimiters, separators, null terminators, and line breaks could easily reside in the middle of actual data.

    MS SQL Server has XML data type for variables, stored procedures parameters, and table columns, as well as XML indexes. SQL Server supports XML validation against XSD schemas.

    (X)HTML is a subset of XML.

    Web Services are using XML extensively.

    XML is operating system, vendors, languages, etc. agnostic.

    XML has many existing APIs:

    • XQuery and XPath - to query it.
    • XSD - to validate it for data quality.
    • XSLT - to transform it.
    • XSL-FO - for publishing industry.
    • LINQ to XML - general programmatic API.
    • Etc.

    I recently asked Microsoft to beef up NoSQL functionality in SQL Server. Please check it out, add your comments, and vote for it: https://feedback.azure.com/forums/908035-sql-server/suggestions/38142115-sql-server-vnext-post-2019-and-nosql-functionali


    P.S. All Microsoft Office documents, i.e. Word, Excel, PowerPoint, etc. internally are in XML format.
    Wednesday, September 18, 2019 12:02 AM
  • Hi ITBObbyP,

    This is a really good question! I again went through some books and found valuable information which may give you some ideas. Now I share it with you (from Inside Microsoft SQL Server 2008 T-SQL Programming by Itzik Ben-Gan Page 494)

    --QUOTE

    ……there are very few rules for a well-formed XML document . In an XML document, the actual data is mixed with metadata, such as  element and attribute names . Because XML is text, it is very convenient for exchanging data  between different systems and even between different platforms . However, when exchanging data, it becomes important to have the metadata fixed . If you had to import a document with customers, orders, as in the preceding examples, every couple of minutes, you’d definitely want to automate the importing process . Imagine how hard you’d have to work if the metadata changed with every new import . For example, imagine that the Customer element name changes to Client, and Order to Purchase . Or imagine that the orderdate attribute (or element) suddenly changes its data type from timestamp to integer . You’d quickly conclude that you should have fixed the schema for the XML documents you are importing ….

    ---UNQUOTE

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 18, 2019 2:39 AM