sql server - Reading repeating tags using open xml or xml functions SQL -
<activedig> <dig isprim="true"> <code>3342</code> <name>abc</name> </dig> <dig isprim="false"> <code>5342</code> <name>xyz</name> </dig> </activedig> i'm trying load table follows:
| dig_isprim | code | name | |-------------|--------|--------| | true | 3342 | abc | |-------------|--------|--------| | false | 5342 | xyz |
example
set @xml =' <activedig> <dig isprim="true"> <code>3342</code> <name>abc</name> </dig> <dig isprim="false"> <code>5342</code> <name>xyz</name> </dig> </activedig> ' select dig_isprim = lvl1.n.value('@isprim','varchar(50)') ,code = lvl1.n.value('./code[1]','varchar(50)') ,name = lvl1.n.value('./name[1]','varchar(50)') @xml.nodes('activedig/*') lvl1(n) returns
dig_isprim code name true 3342 abc false 5342 xyz edit -fully dynamic sql
the following uses table-valued function parse virtually xml dataset. have dynamic xml within dynamic xml.
it may seem little complicated, when @ individual parts, not bad.
example
declare @xml xml =' <activedig> <dig isprim="true"> <code>3342</code> <name>abc</name> </dig> <dig isprim="false"> <code>5342</code> <name>xyz</name> </dig> </activedig> ' declare @sql varchar(max) = ' select * #temp ( select * ,item = concat(element,iif(attribute='''','''',''_''),attribute ) ,rown = sum(case when lvl=2 1 else 0 end) on (order r1) [dbo].[tvf-xml-hier](cast('''+cast(@xml varchar(max))+''' xml)) ) value not null declare @sql varchar(max) = '' select * ( select rown,item,value #temp ) pivot (max([value]) [item] in ('' + stuff((select top 1000 '',''+quotename(item) (select distinct item,seq=min(r1) on (partition item) #temp ) order seq xml path('''')),1,1,'''') + '') ) p'' exec(@sql); ' exec(@sql) returns
you may notice thing specified lvl 2... no attribute names or node names.
the udf if interested
create function [dbo].[tvf-xml-hier](@xml xml) returns table return cte0 ( select lvl = 1 ,id = cast(1 int) ,pt = cast(null int) ,element = x.value('local-name(.)','varchar(150)') ,attribute = cast('' varchar(150)) ,value = x.value('text()[1]','varchar(max)') ,xpath = cast(concat(x.value('local-name(.)','varchar(max)'),'[' ,cast(row_number() over(order (select 1)) int),']') varchar(max)) ,seq = cast(1000000+row_number() over(order (select 1)) varchar(max)) ,attdata = x.query('.') ,xmldata = x.query('*') @xml.nodes('/*') a(x) union select lvl = p.lvl + 1 ,id = cast( (lvl + 1) * 1024 + (row_number() over(order (select 1)) * 2) int ) * 10 ,pt = p.id ,element = c.value('local-name(.)','varchar(150)') ,attribute = cast('' varchar(150)) ,value = cast( c.value('text()[1]','varchar(max)') varchar(max) ) ,xpath = cast(concat(p.xpath,'/',c.value('local-name(.)','varchar(max)'),'[',cast(row_number() over(partition c.value('local-name(.)','varchar(max)') order (select 1)) int),']') varchar(max) ) ,seq = cast(concat(p.seq,' ',10000000+cast( (lvl + 1) * 1024 + (row_number() over(order (select 1)) * 2) int ) * 10) varchar(max)) ,attdata = c.query('.') ,xmldata = c.query('*') cte0 p cross apply p.xmldata.nodes('*') b(c) ) , cte1 ( select r1 = row_number() on (order seq),a.* ( select lvl,id,pt,element,attribute,value,xpath,seq cte0 union select lvl = p.lvl+1 ,id = p.id + row_number() on (order (select null)) ,pt = p.id ,element = p.element ,attribute = x.value('local-name(.)','varchar(150)') ,value = x.value('.','varchar(max)') ,xpath = p.xpath + '/@' + x.value('local-name(.)','varchar(max)') ,seq = cast(concat(p.seq,' ',10000000+p.id + row_number() on (order (select null)) ) varchar(max)) cte0 p cross apply attdata.nodes('/*/@*') a(x) ) ) select a.r1 ,r2 = isnull((select max(r1) cte1 seq a.seq+'%'),a.r1) ,a.lvl ,a.id ,a.pt ,a.element ,a.attribute ,a.xpath ,title = replicate('|---',lvl-1)+element+iif(attribute='','','@'+attribute) ,a.value cte1 /* source: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx declare @xml xml='<person><firstname preferred="annie" nickname="bebe">annabelle</firstname><lastname>smith</lastname></person>' select * [dbo].[tvf-xml-hier](@xml) order r1 */ edit 2- visualization
if run following:
select * [dbo].[tvf-xml-hier](@xml) order r1 the results be
Comments
Post a Comment