1

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

Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
Ashish Venugopal
  • 19
  • 2
  • 1
    Please include the query you have so far, even if it's not working. – Stu May 23 '22 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 May 23 '22 at 10:14
  • 2
    @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 May 23 '22 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 May 23 '22 at 11:05

1 Answers1

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43