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:

ID path value
0001 / root Null
0001/0001 / root / a Null
0001/0001/0001 / root / a / @ tp1 test1
0001/0001/0002 / root / a / b Null
0001/0001/0003 / root / a / c test3
0001/0002 / root / a Null
0001/0002/0001 / root / a / @ tp2 test2
0001/0002/0002 / root / a / b Null
0001/0002/0002/0001 / root / a / b / c Null
0001/0002/0002/0001/0001 root / a / b / c / @ tp3 test3
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
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 () 


IDpath path value
0 root Null
0/2 root / a Null
0/2/3 root / a / @ tp1 test1
0/2/4 root / a / b Null
0/2/5 root / a / c test3
0/6 root / a Null
0/6/7 root / a / @ tp2 test2
0/6/8 root / a / b Null
0/6/8/9 root / a / b / c Null
0/6/8/9/10 root / a / b / c / @ tp3 test3
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
 @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 ); 
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
Colleagues, thank you!
invm , special thanks!
This is exactly what you need.

True, the (potentially) query performance plunges me into despondency.
:-)
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
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:
name value id
root Null one
root / a Null 1/1
root / a Null 1/2
root / a / b Null 1/2/1
root / a / b / c Null 1/2/1/1
root / a / b Null 1/1/1
root / a / c test3 1/1/2
root / @ tp1 tescte 1/1
root / @ tp3 tescte3 1/2
root / @ tp2 test2 1/3
root / a / b / @ tp3 test3 1/2/1/4

The problem is here:
root / @ tp1 tescte 1/1
root / @ tp3 tescte3 1/2
root / @ tp2 test2 1/3


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.
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
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 );
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
invm, and still an error :-)

name value id
root Null one
root / a Null 1/1
root / a Null 1/2
root / a / b Null 1/2/1
root / a / b / c Null 1/2/1/1
root / a / b Null 1/1/1
root / a / c test3 1/1/2
root / a / @ tp1 tescte 1/1/1
root / a / @ tp3 tescte3 1/1/2
root / a / @ tp2 test2 1/2/1
root / a / b / c / @ tp3 test3 1/2/1/1/1


root / a / b Null 1/1/1
root / a / c test3 1/1/2
root / a / @ tp1 tescte 1/1/1
root / a / @ tp3 tescte3 1/1/2

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?
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
uaggster
So?
If so, why not?

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 )  
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
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 ; 
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
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:
0001/0001 root / a
0001/0001/0001 root / a / @ attrib1
0001/0001/0002 root / a / @ attrib2
0001/0001/0003 root / a / c /

Ie, it turns out, and the option "so" does not fit!
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
uaggster
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.
And if there is no child tag?
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
invm, well ... the attribute is a kind of "child tag".
Is not it so?
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
uaggster
invm, well ... the attribute is a kind of "child tag".
Is not it so?
Yes. So he has an ID at this level.
I still do not understand what is wrong with the result.
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
invm
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 ; 


id name value
0000000001 root Null Ok
0000000001/0000000001 root / a Null Ok
0000000001/0000000001/0000000001 root / a / b Null Ok
0000000001/0000000001/0000000002 root / a / c test3 Ok
0000000001/0000000002 root / a Null Ok
0000000001/0000000002/0000000001 root / a / b Null Ok
0000000001/0000000002/0000000001/0000000001 root / a / b / c Null Ok
0000000001/0000000002/0000000001/0000000002 root / a / b / c / @ tp3 test3 Incorrect; the @ tp3-attribute belongs to and must have an id of the type 0000000001/0000000002/0000000001/0000000002/0000000001
0000000001/0000000003 root / a / @ tp1 tescte Incorrect; the @ tp1-attribute belongs to the first a and must have an id of the type 0000000001/0000000001/0000000000
0000000001/0000000004 root / a / @ tp3 tescte3 Incorrect; the @ tp3-attribute belongs to the first a and must have an id of the type 0000000001/0000000001/0000000001
0000000001/0000000005 root / a / @ tp2 test2 Incorrect; the @ tp2-attribute belongs to the second a and must have an id of the type 0000000001/0000000002/0000000000

Wrong ID - for example. Of course, the specific values ​​must be non-recurring, taking into account the correctly processed tags.
Re: Is it possible to deploy xml into an EAV table with one universal query? [new]
uaggster

Yes, in this version the stone flower will not come out ...



Old articles you may read here
Go to Main page






ZZZZZZZZZZZZ Loktionov Anatoly