Using SQL Server 2005, is it possible to combine XML and add an attribute at same time?
Unfortunately, due to project restrictions, I need a SQL Server 2005 solution.
Consider the following, where I need to combine XML from multiple rows within a new <root> element...
; WITH [TestTable] AS (
SELECT 1 AS [PkId], CAST('<data><id>11</id></data>' AS XML) AS [Data]
UNION ALL
SELECT 2, CAST('<data><id>22</id></data>' AS XML)
UNION ALL
SELECT 3, CAST('<data><id>33</id></data>' AS XML)
)
SELECT (
SELECT XMLDATA as [*]
FROM (
SELECT [Data] AS [*]
FROM [TestTable]
FOR XML PATH(''), TYPE
) AS DATA(XMLDATA)
FOR XML PATH('root')
)
This produces the desired output of...
<root>
<data><id>11</id></data>
<data><id>22</id></data>
<data><id>33</id></data>
</root>
But what I need to do, if possible, is add an attribute to the existing data element in each of the rows with the PkId value. The desired output would then look like this...
<root>
<data pkid="1"><id>11</id></data>
<data pkid="2"><id>22</id></data>
<data pkid="3"><id>33</id></data>
</root>
My gut feeling is that this is going to be impossible without the use of a cursor, but if anybody knows a way of doing it I'd love to hear it.
Aucun commentaire:
Enregistrer un commentaire