March 31, 2020 at 8:24 am
Hello,
I'm trying to convert multiple rows with shared IDs into XML however I am struggling getting it collated into a single row per ID.
I currently have the XML looking along these lines:
<results>
<ID>34599</ID>
<name>C: </name>
<totalSize>222.98 GB</totalSize>
</results>
<results>
<ID>34599</ID>
<name>D: </name>
<totalSize>931.39 GB</totalSize>
</results>
<results>
<ID>34603</ID>
<name>C: </name>
<totalSize>222.98 GB</totalSize>
</results>
<results>
<ID>34603</ID>
<name>D: </name>
<totalSize>931.39 GB</totalSize>
</results>
and I'm looking for it like this:
<Results>
<IDs>
<ID>34599</ID>
<drives>
<name>C: </name>
<totalSize>222.98 GB </totalSize>
<name>D: </name>
<totalSize>931.39 GB </totalSize>
</drives>
</IDs>
<IDs>
<ID>34603</ID>
<drives>
<name>C: </name>
<totalSize>222.98 GB </totalSize>
<name>D: </name>
<totalSize>931.39 GB </totalSize>
</drives>
</IDs>
</Results>
the SQL I've written is below:
SELECT ID as [ID]
, h.d.value('(./name)[1]','NVARCHAR(100)')+' ' AS [name]
, h.d.value('(./totalSize)[1]','NVARCHAR(100)')+' ' AS [totalSize]
FROM Table1
CROSS APPLY HardDriveXML.nodes('drives/drive') h(d)
FOR XML PATH('Results')
April 1, 2020 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply