T-SQL循环遍历XML数据列以派生唯一的路径集

我有XML数据列,其中包含问题和答案作为应用程序过程的一部分.
我试图通过T-sql /动态sql实现的是在有目标标记的地方导出一组唯一的路径.
所以对于下面的xml示例,我会期待类似的东西

想要使用它并循环遍历XML以获得所需标记的值.即

[DATA] .value(‘(/ log / clients / client / section / questions / groupone / question / target’,’NVARCHAR(MAX)’)

问题是每个应用程序都有不同的问题集和xml结构,即某些可能有更多问题,有些可能有不同的分组.
然而,我想要的只是如果有一个标签,那么它的路径是什么.

我怎样才能做到最好？

<log>
  <clients>
   <client>
    <section name ="Apps”> 
     <questions>
      <groupone>
       <question>
        <target>Age</target>
       </question>
       <question>
        <target> Height</target>
       </question>
       <question>
        <target> Weight</target>
       </question>
      </groupone>
      <grouptwo name = "exercise">
       <wording>what is your name</wording>
        <question>
         <id>1</id>
         <target>def<target>
        </question>
      </grouptwo>
     </questions>
    </section>
   </client>
  </clients>
 </log>@H_403_17@

解决方法

使用FROM OPENXML的过时方法可能是一个选项. Check this answer.

在this link,你会发现John Cappelletti不时发布的一个函数,它会破坏任何XML(函数代码下面的信用).

但我不确定,你真正想要实现的目标……为什么你需要这条道路？如果您对所有目标节点的值感兴趣,可以执行类似这样的操作(使用//深度搜索不需要精确的XPath)

SELECT t.value(N'(text())[1]','nvarchar(max)')
 FROM @xml.nodes('//target') AS A(t);@H_403_17@ 
 如果你真的需要所有的东西,你可以检查一下： 
  
 CREATE FUNCTION [dbo].[udf-XML-Hier](@XML xml)

Returns Table 
As Return

with  cte0 as ( 
                  Select Lvl       = 1,ID        = Cast(1 as int),Pt        = Cast(NULL as int),Element   = x.value('local-name(.)','varchar(150)'),Attribute = cast('' as varchar(150)),Value     = x.value('text()[1]','varchar(max)'),XPath     = cast(concat(x.value('local-name(.)','[',cast(Row_Number() Over(Order By (Select 1)) as int),']') as varchar(max)),Seq       = cast(1000000+Row_Number() over(Order By (Select 1)) as varchar(max)),AttData   = x.query('.'),XMLData   = x.query('*') 
                  From   @XML.nodes('/*') a(x) 
                  Union  All
                  Select Lvl       = p.Lvl + 1,ID        = Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10,Pt        = p.ID,Element   = c.value('local-name(.)',Value     = cast( c.value('text()[1]','varchar(max)') as varchar(max) ),XPath     = cast(concat(p.XPath,'/',c.value('local-name(.)',cast(Row_Number() Over(PARTITION BY c.value('local-name(.)','varchar(max)') Order By (Select 1)) as int),']') as varchar(max) ),Seq       = cast(concat(p.Seq,' ',10000000+Cast( (Lvl + 1) * 1024 + (Row_Number() Over(Order By (Select 1)) * 2) as int ) * 10) as varchar(max)),AttData   = c.query('.'),XMLData   = c.query('*') 
                  From   cte0 p 
                  Cross  Apply p.XMLData.nodes('*') b(c) 
              ),cte1 as (   
                  Select R1 = Row_Number() over (Order By Seq),A.*
                  From  (
                          Select  Lvl,ID,Pt,Element,Attribute,Value,XPath,Seq From cte0
                          Union All
                          Select Lvl       = p.Lvl+1,ID        = p.ID + Row_Number() over (Order By (Select NULL)),Element   = p.Element,Attribute = x.value('local-name(.)',Value     = x.value('.',XPath     = p.XPath + '/@' + x.value('local-name(.)',10000000+p.ID + Row_Number() over (Order By (Select NULL)) ) as varchar(max))
                          From   cte0 p 
                          Cross  Apply AttData.nodes('/*/@*') a(x) 
                        ) A 
               )

Select A.R1,R2  = IsNull((Select max(R1) From cte1 Where Seq Like 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
 From  cte1 A

/*
Source: http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

Taken from John Cappelletti: https://stackoverflow.com/a/42729851/5089204

Declare @XML xml='<person><firstname preferred="Annie" nickname="BeBe">Annabelle</firstname><lastname>Smith</lastname></person>'
Select * from [dbo].[udf-XML-Hier](@XML) Order by R1
*/
GO 

DECLARE @xml XML=
'<log>
  <clients>
   <client>
    <section name ="Apps"> 
     <questions>
      <groupone>
       <question>
        <target>Age</target>
       </question>
       <question>
        <target> Height</target>
       </question>
       <question>
        <target> Weight</target>
       </question>
      </groupone>
      <grouptwo name = "exercise">
       <wording>what is your name</wording>
        <question>
         <id>1</id>
         <target>def</target>
        </question>
      </grouptwo>
     </questions>
    </section>
   </client>
  </clients>
 </log>';

 SELECT * FROM dbo.[udf-XML-Hier](@xml);
GO@H_403_17@

T-SQL循环遍历XML数据列以派生唯一的路径集

解决方法

猜你在找的MsSQL相关文章