T-SQL遍历XML数据列以派生唯一的路径集 [英] T-SQL looping through XML data column to derive unique set of paths
问题描述
我有一个XML数据列，其中包含问答，这是应用程序过程的一部分. 我试图通过T-SQL/动态SQL实现的是在存在 target 标记的任何地方派生唯一的路径集. 因此，对于下面的xml示例，我希望得到类似的
I have XML data column which contains question and answer as part of an application process. What I am trying to achieve through T-SQL/ Dynamic SQL is to derive a unique set of path wherever there is a target tag. So for the below xml example, I would expect something like
日志/客户/客户/部分/问题/groupone/问题/目标 日志/客户/客户/部分/问题/grouptwo/问题/目标
log/clients/client/section/questions/groupone/question/target log/clients/client/section/questions/grouptwo/question/target
想法将使用它并遍历XML以得出所需标签的值.即
Idea is to then to use this and loop through the XML to derive the values of the desired tags. I.e
[DATA] .value('((/log/clients/client/section/questions/groupone/question/target'，'NVARCHAR(MAX)')
问题是每个应用程序都有不同的问题集和xml结构，即某些应用程序可能有更多问题，某些应用程序可能有不同的分组. 但是我想要的就是如果有一个标签，那么它的路径是什么.
Problem is each application has different set of questions and xml structure i.e. some might have more questions, some might have different grouping. However all I want is if there is a tag then what is its path.
我怎样才能最好地做到这一点?
How can I best achieve this?
<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>
推荐答案
在这里可以选择使用FROM OPENXML的过时方法. 选中此答案.
The outdated approach with FROM OPENXML might be an option here. Check this answer.
在此链接中，您会发现John Cappelletti有时会发布的函数，该函数会切碎任何XML(功能代码下方的信用.)
At this link you'll find a function John Cappelletti posted from time to time, which will shred any XML (credits below the function's code).
但是我不确定，您真正想要实现的目标...为什么需要这条路?如果您对所有目标节点的值感兴趣，则可以执行以下操作(使用//进行深度搜索不需要确切的XPath)
But I'm not sure, what you are really trying to achieve... Why do you need the path? If you are interested in the values of all target nodes you might do something like this (deep search with // does not need the exact XPath)
SELECT t.value(N'(text())[1]','nvarchar(max)')
FROM @xml.nodes('//target') AS A(t);
如果您确实需要所有 ，则可以进行以下检查:
If you really need all and everything you can check this:
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(.)','varchar(max)'),'[' ,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(.)','varchar(150)')
,Attribute = cast('' as varchar(150))
,Value = cast( c.value('text()[1]','varchar(max)') as varchar(max) )
,XPath = cast(concat(p.XPath,'/',c.value('local-name(.)','varchar(max)'),'[',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))
,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() 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
这篇关于T-SQL遍历XML数据列以派生唯一的路径集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
