Turn multiple rows With Similar IDs to XML

• sean.smith 80376

  SSC Rookie

  Points: 37

  More actions

  March 31, 2020 at 8:24 am

  #3739211

  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')
  

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 1, 2020 at 9:10 am

  #3739540

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.