CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Lloyd Banks
Lloyd Banks

Reputation: 36699

XML PATH Concatenate Syntax

I use a nested query to concatenate row data for an aggregate. The syntax I use within the nested query is 

for XML PATH ('')

Can someone explain what is supposed to go between the quotes above? I did some tests and it looks like it just encloses my row data in start and ending HTML like tags.

Upvotes: 0

Views: 1701

Answers (2)

Ben Thul
Ben Thul

Reputation: 32737

The empty string ('') is a special case. Your observation is correct in that whatever you put there will surround your row with open and close XML tags of whatever you put in there. For instance, if you say "for xml path ('a')", it will surround your results with <a> </a>.

Upvotes: 0

XN16
XN16

Reputation: 5889

I'm not 100% sure what you mean, but if your query is similar to the following then that might help you:

SELECT    R.RegionID  AS [@RID],
          R.Enabled   AS [@Enabled],
          (
          SELECT      ST.TypeID  AS [@TID], 
                      QT.[Name] AS [@QTName],   
          FROM        Type ST
          INNER JOIN  QuarryType QT ON ST.QuarryTypeID = QT.QuarryTypeID
          WHERE       R.RegionID = ST.RegionID  
          FOR XML PATH ('QuarryType'), TYPE
          )
FROM      Region R          
FOR XML PATH ('Region'), ELEMENTS, ROOT('root')

As you can see the text in each FOR XML PATH ('') block is nothing special, not related to the data anyway. It is used for element names in your XML output like this:

<root>
    <Region RID="123" Enabled="true">
        <QuarryType TID="4" QTName="Quarry 1"/>
        <QuarryType TID="7" QTName="Quarry 2"/>
        <QuarryType TID="9" QTName="Quarry 5"/>
    </Region>
</root>

If you need more help, just ask!

Upvotes: 1

Related Questions