lundi 23 mars 2015

Combine multiple XML using TSQL, appending data at the same time

In SQL Server 2005, is it possible (without masses of complex code) to combine XML held in a column of multiple rows, and also added extra data at the same time?


Unfortuantely, due to project restrictions, I need SQL Server 2005 compliant answers


For instance, say I have the following schema / data...



PKID Saved Data
1 2015-01-01 12:34 '<data><id>11</id><id>22</id></data>'
2 2015-02-03 14:56 '<data><id>22</id><id>33</id></data>'


I'd like to produce the following XML, so each of the Data columns are combined, and the pkid attribute is added to the <data> node...



<root>
<data pkid='1'><id>11</id><id>22</id></data>
<data pkid='2'><id>22</id><id>33</id></data>
<root>


At first I simply needed to combine the XML into a single set of <id> nodes, so I created the following...



SELECT D.value('.','int')
FROM [MyTable]
CROSS APPLY [Data].nodes('/data/id') AS D(D)
FOR XML PATH('id'), ROOT('data')


This produced the required XML of <data><id>1</id><id>2</id><id>1</id><id>3</id></data>.


But then I realised that I needed to keep the individual <data> nodes separate and with specific unique ids... so the above does not meet my needs.


Unfortunately I'm unable to come up with anything even close to a working answer... so am unable to show you "what I have done so far" (beyond the above), as I simply don't have anything yet.


Aucun commentaire:

Enregistrer un commentaire