How to extract multiple values from XML value column in SQL

Question

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

Please include the query you have so far, even if it's not working. — Stu
– Stu, Commented May 23, 2022 at 10:04
XML in relational database and doing string parsing? That's usually not a good idea, especially violates already 1NF (no column containing more than one data point). You might rethink your database design... — Aconcagua
– Aconcagua, Commented May 23, 2022 at 10:14
@Aconcagua SQL Server has built in XML support, and the XML should be stored in the xml data type, meaning that the XML most be well formed and valid. Storing XML doesn't break NF forms as much as doing something like storing delimited data, as the XML should be well constructed. It does have its uses; though if you need to constantly query the data (in the XML) within the database, then a normalised approach is the correct solution, yes. — Thom A
– Thom A ♦, Commented May 23, 2022 at 10:21
hi, I am able to retrienve the first or second using the query xmlcol.value('(/SAP/Accs/Acc/@number)[1]','int'), but a single column can contain multiple values for Acc num. I am not sure on how to handle it in a single query — Ashish Venugopal
– Ashish Venugopal, Commented May 23, 2022 at 11:05

Charlieface · Accepted Answer · 2022-05-23 11:22:26Z

1

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

answered May 23, 2022 at 11:22

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to extract multiple values from XML value column in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related