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