question

mirons avatar image
mirons asked

In a SQL db, how do I extract values from a xml column

Hi I have a SQL Server 2008 R2 table (billsumrec) which has a column (props) that stores xml. See attached[link text][1] some samples. I want to extract all of the Supplement Names and corresponding Supplement Values. In one record there may be 5, in the next only 1, in the next 3. From Sample1, I would expect: Veterans Supplement, 0.00 Oxygen Supplement, 0.00 Dementia Supplement, 40.10 Package Subsidy, 400.90 Enteral Feeding Supplement, 0.00 Income Tested......, -32.80 [1]: /storage/temp/4172-samples.docx
xmlsql server 2008 r2
samples.docx (13.1 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
This answer uses a lot of different techniques, so take care to review them all to fully understand what is going on. First I created a table with your data in it, each chunk of XML has a separate ID. The first step is to extract the values from the XML - this is done by using the `query()` and `nodes()` xml methods on the `xml` datatype. Then we have pipe-delimited data, and to split that out I used a tally table (cte). I could then reference that `cte_split` 3 times to build up the final select. declare @YourTable table (ID int,Propbag xml); insert into @YourTable (ID, Propbag) select 1, ' '; insert into @YourTable (ID, Propbag) select 2, ' '; insert into @YourTable (ID, Propbag) select 3, ' '; WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) ,cte_PipeDelimited (ID, Name, Value) as ( select ID, a.query('.').value('(Prop/@Name)[1]', 'varchar(100)') , '|'+ a.query('.').value('(Prop/@Value)[1]', 'varchar(100)') +'|' from @Yourtable cross apply Propbag.nodes('/PropBag/Prop') as Prop(a) ) ,cte_split (ID, Name, rn, Value) as ( select ID,Name,row_number()over(partition by ID, Name order by N) as rn, substring(Value,N+1,charindex('|',Value,N+1)-N-1) as Value from cte_PipeDelimited join tally_cte on N < len(Value) and substring(Value,N,1) = '|' ) select SupplementNames.ID, SupplementNames.Value, SupplementPeriods.Value, SupplementValues.Value from (select ID, Name, rn, Value from cte_split where Name='Supplement Names') SupplementNames join (select ID, Name, rn, Value from cte_split where Name='Supplement Periods') SupplementPeriods on SupplementPeriods.ID = SupplementNames.ID and SupplementPeriods.rn = SupplementNames.rn join (select ID, Name, rn, Value from cte_split where Name='Supplement Values') SupplementValues on SupplementValues.ID = SupplementNames.ID and SupplementValues.rn = SupplementNames.rn ID Value Value Value ----------- ------------------------------------------------------ -------- -------- 1 Veterans Supplement ptDay 0.00 1 Oxygen Supplement ptDay 0.00 1 Dementia Supplement ptDay 40.10 1 Package Subsidy ptDay 400.90 1 Enteral Feeding Supplement ptDay 0.00 2 Package Subsidy ptDay 400.90 3 Package Subsidy ptDay 1339.90 3 Top-up Supplement ptDay 0.00 3 Income Tested Care Fee daily subsidy offset amount ptDay -144.90 (9 row(s) affected)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mirons avatar image mirons commented ·
Wow. Thank you Kev, I'll give it a try.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.