XML 到 SQL 的问题 - SQL Server

XML to SQL Issue - SQL Server(XML 到 SQL 的问题 - SQL Server)
本文介绍了XML 到 SQL 的问题 - SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我试图在 sql 脚本中获取 xml 特定元素的位置,但我正在获取特定元素的所有位置的详细信息,因为我只是在寻找特定级别.

I am trying to get position of a specific element of xml in sql script , but I am getting details for all the positions of a specific element , where as I am looking for a particular level only.

这是代码:

declare @xmlVar xml ='
<A specVersion="2.09">
  <B id="1" type="Regular">
    <C>
      <D>
        <E actioncode="A" date="06/13/2018 09:20" />
        <E  actioncode="B" date="06/13/2018 09:20" />
      </D>
      <D>
        <E actioncode="C" date="06/13/2018 09:20" />
      </D>
    </C>
  </B>

  <B id="2" type="Regular">
    <C>
      <D>
        <E  actioncode="D" date="06/13/2018 09:20" />
      </D>
    </C>
    <F>
        <D>
            <E  actioncode="F" date="06/13/2018 09:20" />
        </D>
    </F>
  </B>
</A>' ;

WITH Tally(Nmbr) AS
(
    SELECT TOP (SELECT @xmlVar.value(N'count(//D)','int'))
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM master..spt_values --just a pre-filled table with many rows
)
SELECT Nmbr
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM Tally
OUTER APPLY(SELECT  @xmlVar.query(N'//D').query(N'/D[sql:column("Nmbr")]')) AS B(ds)
OUTER APPLY ds.nodes(N'D/E') AS C(e);

以上查询的结果:

1   A
1   B
2   C
3   D
4   F

预期输出:

1 A
1 B
2 C
3 D

我正在寻找路径 A->B->C->D 中所有 D 的位置,而不是其他路径中的位置.

I am looking for positions of all D which comes in path A->B->C->D not the one's which come in other path.

推荐答案

好的,对于您之前的问题,这似乎是一个相当容易的更改.您可以使用相同的逻辑,但将其缩减为您想要查看的路径:

Okay, this seems to be a rather easy change to your previous question. You can use the same logic, but reduce it to the path you want to see:

WITH Tally(Nmbr) AS
(
    SELECT TOP (SELECT @xmlVar.value(N'count(/A/B/C/D)','int'))
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    FROM master..spt_values --just a pre-filled table with many rows
)
SELECT Nmbr
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM Tally
OUTER APPLY(SELECT  @xmlVar.query(N'/A/B/C/D').query(N'/D[sql:column("Nmbr")]')) AS B(ds)
OUTER APPLY ds.nodes(N'D/E') AS C(e);

更新:获取

正如您所发现的,上述方法不能向后移动到 .试试这个:

WITH AllBs AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS B_position
          ,b.value('@id','int') AS B_id
          ,b.query('.') AS B_node
    FROM @xmlVar.nodes('/A/B') AS A(b)
)
,AllDs As
(
    SELECT AllBs.*
          ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS D_position
          ,d.query('.') AS D_node 
    FROM AllBs 
    OUTER APPLY B_node.nodes('B/C/D') AS A(d)
)
SELECT AllDs.*
      ,e.value(N'@actioncode[1]','nvarchar(max)') AS Employee
FROM AllDs
OUTER APPLY D_node.nodes('D/E') AS A(e);

正如我在第一个答案中所写的,排序顺序存在很小的风险......但是使用 tally 表 的方法无法获取第三次出现,因为 [sql:column()] 与任何父项重新编号.这意味着:第三个 是第二个 中的第一个.这就是为什么我将 .query() 放在中间......

As written in my first answer, there's a tiny risk about the sort order... But the approach with the tally table cannot fetch the 3rd occurance, because the position within [sql:column()] is re-numbered with any parent. That means: The 3rd <D> is the 1st in the 2nd <B>. That's why I placed the .query() in between...

这篇关于XML 到 SQL 的问题 - SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)