Microsoft SQL Server FOR XML PATH Specifying structure using XPath expressions
Example
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')
