What Is the Meaning of Select ... for Xml Path(' '),1,1)

What is the meaning of FOR XML PATH(' ')?

There seems to be a bit of confusion about "Root" and "Element". In XML, the root is a single node at the top of the tree.

If we specify FOR XML PATH with nothing following, then we get a set of XML elements called "Row". This is a default name.

USE AdventureWorks2016CTP3;
GO

SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH;

We can specify a different name for the enclosing XML element like this.

SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('Person');

If we want the XML elements to be enclosed in a top level root element, we can specify it like this.

SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('Person'),
    ROOT('People');

Specifying an empty string for the element name, causes it to leave out the enclosing XML elements. It seems a very peculiar thing to do. I can't imagine why anyone would want to do that.

SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('');

However Books Online does say this is the correct behaviour. See Basic Syntax of the FOR XML Clause > https://msdn.microsoft.com/en-gb/library/ms190922.aspx > Arguments > PATH.

If you provide an empty string (FOR XML PATH ('')), no wrapper element is generated.

Why FOR XML PATH() is used in this script?

FOR XML PATH('') is used in older versions of SQL server (pre 2017) to get values from multiple rows into one. Since you're setting a variable, @SQL, you're setting a single string and therefore need one row returned.

FOR XML PATH('') produces all the MAX(CASE FieldName [...] with data from the tblValuationSubGroup table. Each row in tblValuationSubGroup contains one field. FOR XML PATH('') adds the rows into one string, which is later turned into fields during execution.

The issue FOR XML PATH('') solves in older versions of SQL Server has been solved with the STRING_AGG() function since SQL Server 2017.
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

SQL Select FOR XML PATH - Combine ordering and concatenate string with increasing counter

You just need to order the rows:

DECLARE @t TABLE (Code VARCHAR(255), Filename VARCHAR(255), ImageOrder INT)

INSERT INTO @t VALUES
('xxxx', 'Noimage.jpg', 0),
('xxxx', 'yyyy.jpg', 2),
('xxxx', 'zzzz.jpg', 1),
('xxxx', 'aaaa.jpg', 3)
 
SELECT REPLACE(
  (SELECT CAST('&p' + CAST(ImageOrder AS varchar) + '=' + Filename AS VARCHAR(MAX))
  FROM @t
  WHERE ImageOrder <> 0
  ORDER BY ImageOrder
  FOR XML PATH (''))
  , 'amp;', '') AS Txt

Which will give you the result:

&p1=zzzz.jpg&p2=yyyy.jpg&p3=aaaa.jpg

Using GROUP BY with FOR XML PATH in SQL Server 2016

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID int, Comment nvarchar(150));
INSERT INTO @tbl VALUES 
(1006, 'I'),
(1006, 'am'),
(1006, 'good'),
(2, 'You'),
(2, 'are'),
(2, 'awesome');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT p.ID
   , STUFF((SELECT @separator + Comment 
          FROM @tbl AS c
          WHERE c.ID = p.ID
          FOR XML PATH('')), 1, LEN(@separator), '') AS Result
FROM @tbl AS p
GROUP BY p.ID
ORDER BY p.ID;

Output

+------+-----------------+
|  ID  |     Result      |
+------+-----------------+
|    2 | You are awesome |
| 1006 | I am good       |
+------+-----------------+

Using CHAR(13) in a FOR XML SELECT

Thanks everyone for your help.

The ultimate goal here was to present the data in Excel as part of a report. I'm sure there is a more elegant way to do this, but I at least got the results I wanted by doing this:

REPLACE (
    REPLACE(
        REPLACE(
            (SELECT Comment FROM CallNotes WHERE ForeignId = a.ForeignId FOR XML PATH (''))
        , '<Comment>', '')  
    , '</Comment>', CHAR(13) + CHAR(10))
, '
', '') AS Comments

The select statement all by itself returns XML as we would expect:

<comment>This is a comment</comment><comment>This is another comment</comment>

The inner most REPLACE just gets rid of the opening tag:

<comment> 

The middle REPLACE removes the closing tag:

</comment> 

and replaces it with CHAR(13) + CHAR(10). And the outer most REPLACE gets rid of this:

(I still don't understand where that's coming from.)

So, when the results are sent to Excel, it looks like this inside the cell:

This is a comment.
This is another comment.

Which is exactly what I want. Again, I'm sure there is a better solution. But this at least is working for now.

---

Related Topics