CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Ashish Venugopal
Ashish Venugopal

Reputation: 19

How to extract multiple values from XML value column in SQL

I have a column with XML data as below

    <SAP status="1" joint="False">
      <Accs>
         <Acc num="1" suffix="14"/>
         <Acc num="2" suffix="15" />
      </Accs>
      <Post>04/27/2022</Post>
      <R>0</R>
    </SAP>

How can I extract both Acc num and Suffix in SQL ? I want the result as

Acc num , Suffix
    1      14
    2      15

Thanks

Upvotes: 1

Views: 1168

Answers (1)

Charlieface
Charlieface

Reputation: 71168

You need XQuery here. You can shred the XML into separate rows with .nodes then use .value to pull out the attribute values

SELECT
  AccNum  = x1.acc.value('@num'   , 'int'),
  Suffix  = x1.acc.value('@suffix', 'int')
FROM YourTable t
CROSS APPLY t.xmlColumn.nodes('SAP/Accs/Acc') x1(acc);

If you also want the data from the SAP root node then you can feed one .nodes into another:

SELECT
  AccNum  = x2.acc.value('@num'   , 'int'),
  Suffix  = x2.acc.value('@suffix', 'int'),
  Post    = x1.SAP.value('(Post/text())[1]', 'date'),
  R       = x1.SAP.value('(R/text())[1]', 'int')
FROM YourTable t
CROSS APPLY t.xmlColumn.nodes('SAP') x1(SAP)
CROSS APPLY x1.SAP.nodes('Accs/Acc') x2(acc);

db<>fiddle

Upvotes: 1

Related Questions