question

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)