Getting started with Microsoft SQL Server OVER Clause PIVOT / UNPIVOT Database Snapshots Retrieve information about the database The STUFF Function FOR XML PATH Cursors Join Common Table Expressions Move and copy data around tables Dates Limit Result Set Retrieve Information about your Instance With Ties Option Variables JSON in Sql Server Window functions Partitioning Stored Procedures GROUP BY Generating a range of dates COALESCE Split String function in Sql Server INSERT INTO CREATE VIEW String Functions Resource Governor ORDER BY WHILE loop System database - TempDb Migration Primary Keys MERGE Full-Text Indexing FOR JSON SELECT statement DBMAIL Index Queries with JSON data Storing JSON in SQL tables OPENJSON Ranking Functions Trigger Converting data types NULLs Transaction isolation levels Advanced options IF...ELSE TRY/CATCH Data Types User Defined Table Types Table Valued Parameters In-Memory OLTP (Hekaton)Temporal Tables Insert Sequences SCOPE_IDENTITY()Views Use of TEMP Table Scheduled Task or Job Isolation levels and locking Sorting/ordering rows Privileges or Permissions Foreign Keys SQLCMD File Group cross apply Basic DDL Operations in MS SQL Server Computed Columns UNION Subqueries Last Inserted Identity CLUSTERED COLUMNSTORE Parsename Installing SQL Server on Windows Aggregate Functions Querying results by page Schemas Backup and Restore Database Transaction handling Natively compiled modules (Hekaton)Database permissions Spatial Data Dynamic SQL Pagination Query Hints Modify JSON text Row-level security Dynamic data masking Export data in txt file by using SQLCMD Encryption Managing Azure SQL Database Common Language Runtime Integration Delimiting special characters and reserved words CASE Statement DBCC BULK Import Query Store Service broker Analyzing a Query Microsoft SQL Server Management Studio Shortcut Keys Permissions and Security PHANTOM read Filestream Drop Keyword String Aggregate functions in SQL Server SQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical Functions Dynamic SQL Pivot Alias Names in Sql Server bcp (bulk copy program) Utility

FOR XML PATH

Remarks:

There are also several other FOR XML modes:

FOR XML RAW - Creates one <row> element per row.
FOR XML AUTO - Attempts to heuristically autogenerate a hierarchy.
FOR XML EXPLICIT - Provides more control over the shape of the XML, but is more cumbersome than FOR XML PATH.

Hello World XML

SELECT 'Hello World' FOR XML PATH('example')

<example>Hello World</example>

Specifying namespaces

SQL Server 2008

WITH XMLNAMESPACES (
    DEFAULT 'http://www.w3.org/2000/svg',
    'http://www.w3.org/1999/xlink' AS xlink
)
SELECT 
    'example.jpg' AS 'image/@xlink:href',
    '50px' AS 'image/@width',
    '50px' AS 'image/@height'
FOR XML PATH('svg')

<svg xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://www.w3.org/2000/svg">
    <image xlink:href="firefox.jpg" width="50px" height="50px"/>
</svg>

Specifying structure using XPath expressions

SELECT
    'XPath example' AS 'head/title',
    'This example demonstrates ' AS 'body/p',
    'https://www.w3.org/TR/xpath/' AS 'body/p/a/@href',
    'XPath expressions' AS 'body/p/a'
FOR XML PATH('html')

<html>
    <head>
        <title>XPath example</title>
    </head>
    <body>
        <p>This example demonstrates <a href="https://www.w3.org/TR/xpath/">XPath expressions</a></p>
    </body>
</html>

In FOR XML PATH, columns without a name become text nodes. NULL or '' therefore become empty text nodes. Note: you can convert a named column to an unnamed one by using AS *

DECLARE @tempTable TABLE (Ref INT, Des NVARCHAR(100), Qty INT)
INSERT INTO @tempTable VALUES (100001, 'Normal', 1), (100002, 'Foobar', 1), (100003, 'Hello World', 2)

SELECT ROW_NUMBER() OVER (ORDER BY Ref) AS '@NUM',
     'REF' AS 'FLD/@NAME', REF AS 'FLD', '',
     'DES' AS 'FLD/@NAME', DES AS 'FLD', '',
     'QTY' AS 'FLD/@NAME', QTY AS 'FLD'
FROM @tempTable 
FOR XML PATH('LIN'), ROOT('row')

<row>
  <LIN NUM="1">
    <FLD NAME="REF">100001</FLD>
    <FLD NAME="DES">Normal</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="2">
    <FLD NAME="REF">100002</FLD>
    <FLD NAME="DES">Foobar</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="3">
    <FLD NAME="REF">100003</FLD>
    <FLD NAME="DES">Hello World</FLD>
    <FLD NAME="QTY">2</FLD>
  </LIN>
</row>

Using (empty) text nodes helps to separate the previously output node from the next one, so that SQL Server knows to start a new element for the next column. Otherwise, it gets confused when the attribute already exists on what it thinks is the "current" element.

For example, without the the empty strings between the element and the attribute in the SELECT statement, SQL Server gives an error:

Attribute-centric column 'FLD/@NAME' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Also note that this example also wrapped the XML in a root element named row, specified by ROOT('row')

Using FOR XML PATH to concatenate values

The FOR XML PATH can be used for concatenating values into string. The example below concatenates values into a CSV string:

DECLARE @DataSource TABLE
(
    [rowID] TINYINT
   ,[FirstName] NVARCHAR(32)
);

INSERT INTO @DataSource ([rowID], [FirstName])
VALUES (1, 'Alex')
      ,(2, 'Peter')
      ,(3, 'Alexsandyr')
      ,(4, 'George');

SELECT STUFF
(
    (
        SELECT ',' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

Few important notes:

the ORDER BY clause can be used to order the values in a preferred way
if a longer value is used as the concatenation separator, the STUFF function parameter must be changed too;

SELECT STUFF
(
    (
        SELECT '---' + [FirstName]
        FROM @DataSource
        ORDER BY [rowID] DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,3 -- the "3" could also be represented as: LEN('---') for clarity
    ,''
);

as the TYPE option and .value function are used, the concatenation works with NVARCHAR(MAX) string

Contributors

Topic Id: 727

Example Ids: 2450,2451,2452,2786

This site is not affiliated with any of the contributors.