Go to Main page..
Old articles you may read here
Go to Main page
Is it possible to deploy xml in EAV table with one universal query? , Microsoft SQL Server
 Is it possible to deploy xml in EAV table with one universal query? [new] | ||||||||||||||||||||||||||||||||||
| Colleagues, welcome! There is an arbitrary xml, for example: root > < root >
a tp1 ="test1" > <a tp1 = "test1">
b / > < b / >
c > test3 < /c > < c > test3 < / c >
/a > < / a >
a tp2 ="test2" > <a tp2 = "test2">
b > < b >
c tp3 ="test3" / > < c tp3 = "test3" / >
/b > < / b >
/a > < / a >
/root > < / root >
Is it possible to convert it with a single query to an EAV table? You should have something like this:
| ||||||||||||||||||||||||||||||||||
| uaggster from something like this you can "push off" @XML_Doc_Handle int declare @XML_Doc xml = '<root> <a tp1="test1"> <b /> <c>test3</c> </a> <a tp2="test2"> <b> <c tp3="test3" /> </b> </a> </root>' -- EXEC sp_xml_preparedocument @XML_Doc_Handle OUTPUT , @XML_Doc ; declare @XML_Doc_Handle int declare @XML_Doc xml = '<root> <a tp1="test1"> <b /> <c> test3 </ c> </a> <a tp2="test2"> <b> <c tp3 = "test3" /> </ b> </a> </ root> ' - EXEC sp_xml_preparedocument @XML_Doc_Handle OUTPUT , @XML_Doc ; SELECT * into #t FROM OPENXML ( @XML_Doc_Handle , '.' , 2 ) -- EXEC sp_xml_removedocument @XML_Doc_Handle ; - SELECT * into #t FROM OPENXML ( @XML_Doc_Handle , '.' , 2 ) - EXEC sp_xml_removedocument @XML_Doc_Handle ; ; --select * from #t ; cte as ( select * , IDpath = cast ( id as varchar ( max )) ,[ path ] = cast ( localname as varchar ( max )) , value = cast ( null as varchar ( max )) , flag =1 from #t where id =0 union all select t .* , case when t . localname= '#text' then cte . IDpath else cte . IDpath + '/' + cast ( t . id as varchar ( max )) end , case when t . localname= '#text' then cte . path else cast ( cte . path + '/' + case when t . nodetype=2 then '@' else '' end + t . localname as varchar ( max )) end , case when t . localname= '#text' then cast ( t . text as varchar ( max )) else null end , case when t . localname= '#text' then 0 else 1 end from #tt inner join cte on t . parentid=cte . id ) select top 1 with ties IDpath , [ path ], value from cte order by row_number () over ( partition by IDpath order by flag ) drop table #t with cte as ( select * , idpath = cast ( id as varchar ( max )) , [ path ] = cast ( localname as varchar ( max )) , value = cast ( null as varchar ( max )) , flag = 1 from # t where id = 0 union all select t . * , case when t . localname = '#text' then cte . IDpath else cte . IDpath + '/' + cast ( t . id as varchar ( max )) end , case when t . localname = '#text' then cte . path else cast ( cte . path + '/' + case when t . nodetype = 2 then '@' else '' end + t . localname as varchar ( max )) end , case when t . localname = '#text' then cast ( t . text as varchar ( max )), still null end , case when t . localname = '#text' then 0 then 1 join from join join cte on t . parentid = cte . id ) select top 1 with ties IDpath , [ path ], order by row_number ()
|
@x xml = N '<root> <a tp1="tescte"> <b /> <c>test3</c> </a> <a tp2="test2"> <b> <c tp3="test3" /> </b> </a> </root>' ; declare @x xml = N '<root> <a tp1="tescte"> <b /> <c> test3 </ c> </a> <a tp2="test2"> <b> <c tp3 = " test3 "/> </ b> </a> </ root> ' ; cte ( x , name , value , id ) as ( select t . n . query ( './*' ), t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'text()[1]' , 'nvarchar(max)' ), cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . nodes ( '/*' ) t ( n ) union all select t . n . query ( './*' ), a . name + '/' + t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'text()[1]' , 'nvarchar(max)' ), a . id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from cte a cross apply a . x . nodes ( '*' ) t ( n ) ) select name , value , id from cte union all select a . with cte ( x , name , value , id ) as ( select t . n . query ( './*' ), t . n . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . value ( 'text () [1]' , 'nvarchar (max)' ), cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . nodes ( ' / * ' ) t ( n ) union all select .n . query ( ' ./* ' ), a . name + ' / ' + t . n . value ( ' local-name (.) ' , ' nvarchar ( max) ' ), t . n . value ( ' text () [1] ' , ' nvarchar (max) ' ), a . id + ' / ' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from a te cross cross apply x x . x . nodes ( '*' ) t ( n ) ) select a . + '/@' + t . name + '/ @' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), t . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( '.' , 'nvarchar(max)' ), a . value ( '.' , 'nvarchar (max)' ), a . + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from cte a cross apply a . id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from a cte a cross apply a . x . ( '*/@*' ) t ( n ); nodes ( '* / @ *' ) t ( n ); |
| Colleagues, thank you! invm , special thanks! This is exactly what you need. True, the (potentially) query performance plunges me into despondency. :-) |
invm, no, all the same error! root > declare @x xml = N ' < root >
a tp1 ="tescte" tp3 ="tescte3" > <a tp1 = "tescte" tp3 = "tescte3">
b / > < b / >
c > test3 < /c > < c > test3 < / c >
/a > < / a >
a tp2 ="test2" > <a tp2 = "test2">
b > < b >
c tp3 ="test3" / > < c tp3 = "test3" / >
/b > < / b >
/a > < / a >
/root > '; < / root > ';
cte ( x , name , value , id ) as with cte ( x , name , value , id ) as ( select t . n . ( './*' ), query ( './*' ), t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( 'text()[1]' , 'nvarchar(max)' ), value ( 'text () [1]' , 'nvarchar (max)' ), ( row_number () over ( order by ( select 1 )) as varchar ( max )) cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . ( '/*' ) t ( n ) nodes ( '/ *' ) t ( n ) all union all select t . n . ( './*' ), query ( './*' ), a . + '/' + t . name + '/' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( 'text()[1]' , 'nvarchar(max)' ), value ( 'text () [1]' , 'nvarchar (max)' ), a . + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from apply cte a cross apply a . x . ( '*' ) t ( n ) nodes ( '*' ) t ( n ) ) name , value , id from cte select name , value , id from cte all union all select a . + '/@' + t . name + '/ @' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( '.' , 'nvarchar(max)' ), value ( '.' , 'nvarchar (max)' ), a . + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from apply cte a cross apply a . x . ( '*/@*' ) t ( n ); nodes ( '* / @ *' ) t ( n ); Gives out:
The problem is here:
I suspect that the problem is here: select tnquery ('./*'), a.name + '/' + tnvalue ('local-name (.)', 'nvarchar (max)'), tnvalue ('text () [1]', 'nvarchar (max)'), a.id + '/' + cast (row_number () over (order by (select 1)) as varchar (max)) from cte a cross apply axnodes ('*') t (n) But I can not fix it. I do not understand how. |
uaggster cte ( x , name , value , id ) as with cte ( x , name , value , id ) as ( select t . n . ( '.' ), query ( '.' ), t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( 'text()[1]' , 'nvarchar(max)' ), value ( 'text () [1]' , 'nvarchar (max)' ), ( row_number () over ( order by ( select 1 )) as varchar ( max )) cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . ( '/*' ) t ( n ) nodes ( '/ *' ) t ( n ) all union all select t . n . ( '.' ), query ( '.' ), a . + '/' + t . name + '/' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( 'text()[1]' , 'nvarchar(max)' ), value ( 'text () [1]' , 'nvarchar (max)' ), a . + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from apply cte a cross apply a . x . ( '*/*' ) t ( n ) nodes ( '* / *' ) t ( n ) ) name , value , id from cte select name , value , id from cte all union all select a . + '/@' + t . name + '/ @' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( '.' , 'nvarchar(max)' ), value ( '.' , 'nvarchar (max)' ), a . + '/' + cast ( row_number () over ( partition by a . id order by ( select 1 )) as varchar ( max )) id + '/' + cast ( row_number () over ( partition by a . id order by ( select 1 )) as varchar ( max )) from apply cte a cross apply a . x . ( '*/@*' ) t ( n ); nodes ( '* / @ *' ) t ( n ); |
invm, and still an error :-)
IDs are the same. Although, probably, for attributes it is logical to have the same level 0 for all. cte ( x , name , value , id ) as ( select t . n . query ( '.' ), t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'text()[1]' , 'nvarchar(max)' ), cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . nodes ( '/*' ) t ( n ) union all select t . n . query ( '.' ), a . name + '/' + t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'text()[1]' , 'nvarchar(max)' ), a . id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from cte a cross apply a . x . nodes ( '*/*' ) t ( n ) ) select name , value , id from cte union all select a . with cte ( x , name , value , id ) as ( select t . n . query ( '.' ), t . n . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . value ( 'text () [1]' , 'nvarchar (max)' ), cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from @x . nodes ( '/ * ' ) t ( n ) union select all t . n . query ( '. ' ), a . name + ' / ' + t . n . value ( ' local-name (.) ' , ' nvarchar (max) ' ) , t . n . value ( 'text () [1]' , 'nvarchar (max)' ), a . id + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) from (x ) nodes ( '* / *' ) t ( n ) ) select name , value , id + '/@' + t . name + '/ @' + t . n . ( 'local-name(.)' , 'nvarchar(max)' ), t . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . ( '.' , 'nvarchar(max)' ), a . value ( '.' , 'nvarchar (max)' ), a . + '/0/' + cast ( row_number () over ( partition by a . id order by ( select 1 )) as varchar ( max )) from cte a cross apply a . id + '/ 0 /' + cast ( row_number () over ( partition by a . id order by ( select 1 )) as varchar ( max )) from a cross to a cross apply a . x . ( '*/@*' ) t ( n ); nodes ( '* / @ *' ) t ( n ); So? |
But, imkho, it will be simpler through edge-table. to proposed court . Just need a little fix: ; cte as ( with cte as ( select * IDpath = cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) , IDpath = cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) path ] = cast ( localname as varchar ( max )) , [ path ] = cast ( localname as varchar ( max )) value = cast ( null as varchar ( max )) , value = cast ( null as varchar ( max )) flag =1 , flag = 1 #t from #t id =0 where id = 0 all union all select t . * case when t . , case when t . then cte . localname = '#text' then cte . cte . IDpath else cte . '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) end IDpath + '/' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )) end case when t . , case when t . then cte . localname = '#text' then cte . cast ( cte . path + '/' + case when t . nodetype=2 then '@' else '' end + t . localname as varchar ( max )) end path else cast ( cte . path + '/' + case when t . nodetype = 2 then '@' else '' end + t . localname as varchar ( max )) end case when t . , case when t . then cast ( t . text as varchar ( max )) else null end localname = '#text' then cast ( t . text as varchar ( max )) else null end case when t . , case when t . then 0 else 1 end localname = '#text' then 0 else 1 end #tt from #tt join cte on t . inner join cte on t . parentid = cte . id ) top 1 with ties select top 1 with ties [ path ], value IDpath , [ path ], value cte from cte by row_number () over ( partition by IDpath order by flag ) order by row_number () over ( partition by IDpath order by flag ) |
Another option cte ( x , name , value , id ) as ( select t . n . query ( '*' ), case when n . pn > '' then n . pn + '/@' + n . ln else n . ln end , v . v , right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from @x . nodes ( '/*, /*/@*' ) t ( n ) cross apply ( select t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'local-name(..)' , 'nvarchar(max)' )) n ( ln , pn ) cross apply ( select case when n . pn > '' then t . n . value ( '.' , 'nvarchar(max)' ) else t . n . value ( './text()[1]' , 'nvarchar(max)' ) end ) v ( v ) union all select t . n . query ( '*' ), a . name + '/' + case when n . pn > '' then n . pn + '/@' + n . ln else n . ln end , v . v , a . id + '/' + right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from cte a cross apply a . x . nodes ( '/*, /*/@*' ) t ( n ) cross apply ( select t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'local-name(..)' with cte ( x , name , value , id ) as ( select t . n . query ( '*' ), case when n . pn> '' then n . pn + '/ @' + n . ln else n . ln end , v . v , right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from @x . nodes ( '/ *, / * / @ * ' ) t ( n ) cross apply ( select t . n . value ( ' local-name (.) ' , ' nvarchar (max) ' ), t . n . value ( ' local-name (..) ' , ' nvarchar (max) ' )) n ( ln , pn ) cross apply ( select case when n . pn> "' then t . n . value ( '.' , 'nvarchar (max)' ) else t . n . './text () [1]' , 'nvarchar (max)' ) end ) v ( v ) union all select t . n . query ( '*' ), a . name + '/' + case when n . pn> '' then n . pn + '/ @' + n . ln else n . ln end , v . v , a . id + '/' + right ( '0000000000' + cast ( row_number () over ( select 1 )) as varchar ( max )), 10 ) from a cte a cross apply a . x . nodes ( '/ *, / * / @ *' ) t ( n ) cross apply ( select t . n . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . value ( 'local-name (..)' 'nvarchar(max)' )) n ( ln , pn ) cross apply ( select case when n . , 'nvarchar (max)' )) n ( ln , pn ) cross apply ( select case when n . then t . pn> '' then t . n . ( '.' , 'nvarchar(max)' ) else t . value ( '.' , 'nvarchar (max)' ) else t . n . ( './text()[1]' , 'nvarchar(max)' ) end ) v ( v ) ) select id , name , value from cte order by id ; value ( './text()[1]' , 'nvarchar (max)' ) end ) v ( v ) ) select id , name , value from cte order by id ; |
| invm, no, the extreme option does not fit. Since Attributes have an ID at the level with tags, and (well, is it logical?) - they must have an ID at the level of a child tag. Since this is, in fact, child tag entities. Those:
Ie, it turns out, and the option "so" does not fit! |
|
| invm, well ... the attribute is a kind of "child tag". Is not it so? |
I still do not understand what is wrong with the result. |
Speech about it: @x xml = N '<root> <a tp1="tescte" tp3="tescte3"> <b /> <c>test3</c> </a> <a tp2="test2"> <b> <c tp3="test3" /> </b> </a> </root>' ; declare @x xml = N '<root> <a tp1="tescte" tp3="tescte3"> <b /> <c> test3 </ c> </a> <a tp2="test2"> <b> <c tp3 = "test3" /> </ b> </a> </ root> ' ; cte ( x , name , value , id ) as ( select t . n . query ( '*' ), case when n . pn > '' then n . pn + '/@' + n . ln else n . ln end , v . v , right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from @x . nodes ( '/*, /*/@*' ) t ( n ) cross apply ( select t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'local-name(..)' , 'nvarchar(max)' )) n ( ln , pn ) cross apply ( select case when n . pn > '' then t . n . value ( '.' , 'nvarchar(max)' ) else t . n . value ( './text()[1]' , 'nvarchar(max)' ) end ) v ( v ) union all select t . n . query ( '*' ), a . name + '/' + case when n . pn > '' then n . pn + '/@' + n . ln else n . ln end , v . v , a . id + '/' + right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from cte a cross apply a . x . nodes ( '/*, /*/@*' ) t ( n ) cross apply ( select t . n . value ( 'local-name(.)' , 'nvarchar(max)' ), t . n . value ( 'local-name(..)' with cte ( x , name , value , id ) as ( select t . n . query ( '*' ), case when n . pn> '' then n . pn + '/ @' + n . ln else n . ln end , v . v , right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from @x . nodes ( '/ *, / * / @ * ' ) t ( n ) cross apply ( select t . n . value ( ' local-name (.) ' , ' nvarchar (max) ' ), t . n . value ( ' local-name (..) ' , ' nvarchar (max) ' )) n ( ln , pn ) cross apply ( select case when n . pn> ' ' then t . n . value ( '. ' , ' nvarchar (max) ' ) else t . n . value ( './text () [1]' , 'nvarchar (max)' ) end ) v ( v ) union all select t . n . query ( '*' ), a . name + '/' + case when n . pn> '' then n . pn + '/ @' + n . ln else n . ln end , v . v , a . id + '/' + right ( '0000000000' + cast ( row_number () over ( order by ( select 1 )) as varchar ( max )), 10 ) from a cte a cross apply a . x . nodes ( '/ *, / * / @ *' ) t ( n ) cross apply ( select t . n . value ( 'local-name (.)' , 'nvarchar (max)' ), t . n . value ( 'local-name (..)' 'nvarchar(max)' )) n ( ln , pn ) cross apply ( select case when n . , 'nvarchar (max)' )) n ( ln , pn ) cross apply ( select case when n . then t . pn> '' then t . n . ( '.' , 'nvarchar(max)' ) else t . value ( '.' , 'nvarchar (max)' ) else t . n . ( './text()[1]' , 'nvarchar(max)' ) end ) v ( v ) ) select id , name , value from cte order by id ; value ( './text()[1]' , 'nvarchar (max)' ) end ) v ( v ) ) select id , name , value from cte order by id ;
Wrong ID - for example. Of course, the specific values must be non-recurring, taking into account the correctly processed tags. |
| uaggster Yes, in this version the stone flower will not come out ... |
Old articles you may read here
Go to Main page