SQL Server XML文件具有多个名为相同的节点 [英] SQL Server XML file with multiple nodes named the same

问题描述

我有这个内部XML，我传递到一个SQL Server存储过程。

，我觉得可以用来实现我想在这里做的事情，但我需要一些指导如何这可以应用到我的场景。

解决方案

没有光标！游标是由魔鬼创造的，以引导可怜的小数据库人从基于集合的思想的光，深入到黑暗的程序方法... ...

请未来问题）：不要粘贴图片！不得不在...中输入我的例子。

和btw：你的在这里使用我的值会很难，建议正确的事情。根据你在那里做什么，实际上可能需要一个游标。但是在这种情况下，你应该从查询中创建光标，就像我所示...

尝试如下：

  DECLARE @xml XML = 
'< roots> 
< root> 
< ID> 5< / ID> 
< LotResults> 
< ID> 13< / ID> 
< Result> 
< ID> 5< / ID> 
< Count> 2< / Count> 
< / Result> 
< / LotResults> 
< LotResults> 
< ID> 13< / ID> 
< Result> 
< ID> 5< / ID> 
< Count> 2< / Count> 
< / Result> 
< / LotResults> 
< StandardComment> 
< ID> 0< / ID> 
< / StandardComment> 
< ReviewComment> 
< ID> 0< / ID> 
< / ReviewComment> 
< / root> 
< root> 
< ID> 44< / ID> 
< LotResults> 
< ID> 444< / ID> 
< Result> 
< ID> 4444< / ID> 
< Count> 2< / Count> 
< / Result> 
< / LotResults> 
< LotResults> 
< ID> 555< / ID> 
< Result> 
< ID> 55< / ID> 
< Count> 2< / Count> 
< / Result> 
< / LotResults> 
< StandardComment> 
< ID> 5< / ID> 
< / StandardComment> 
< ReviewComment> 
< ID> 5< / ID> 
< / ReviewComment> 
< / root> 
< / roots>'; 
  

- 这里是查询

  SELECT r.value（'ID [1]'，'int'）AS root_ID 
，lr.value（'ID [1]'，'int'）AS LotResult_ID 
，lr.value（'（Result / ID）[1]'，'int'）AS LotResult_Result_ID 
，lr.value（'（Result / Count）[1]'，'int'）AS LotResult_Result_Count 
，r.value（'（StandardComment / ID）[1]'，'int'）AS StandardComment_ID 
，r.value（'（ReviewComment / ID）[1]'，'int' ）AS ReviewComment_ID 
 FROM @ xml.nodes（'/ roots / root'）AS A（r）
 CROSS APPLY r.nodes（'LotResults'）AS B（lr）
  




I have this inner XML which I am passing across to a SQL Server stored procedure.

As you can see, it contains multiple root nodes but additionally, it can also contain 1 to 'n' number of LotResults child nodes.

Is there a way I can manipulate this in the stored procedure so that I can retrieve all LotResults/Result nodes for each root node?

So far I have declared the cursor which can deal with the top-level nodes:

DECLARE cur CURSOR FOR
   SELECT tab.col.value('ID[1]','NCHAR(10)') as INT_TransactionID,
          tab.col.value('ResultDateTime[1]','INT') as DAT_ResultDateTime,
          tab.col.value('StandardComment[1]/ID[1]','BIT') as INT_StandardCommentID,
          tab.col.value('ReviewComment[1]/ID[1]','BIT') as INT_ReviewCommentID
     FROM @XML_Results.nodes('/roots/root') AS tab(col)

     OPEN cur
-- loop over nodes within xml document and populate declared variables
    FETCH 
     NEXT 
     FROM cur

     INTO @INT_TransactionID, 
          @DAT_ResultDateTime, 
          @INT_StandardCommentID, 
          @INT_ReviewCommentID 

    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN
    -- use my values here

      END

    -- fetch next record
    FETCH 
     NEXT 
     FROM cur
     INTO @INT_TransactionID, 
          @DAT_ResultDateTime, 
          @INT_StandardCommentID, 
          @INT_ReviewCommentID 

      END

    CLOSE cur;

Note: I found a post describing how to extract nodes with the same name and I feel like it is something that can be used to achieve what I want to do here but I need some guidance on how this can be applied to my scenario.

解决方案

No cursors! Cursor are created by the devil to lead poor little db people away from the light of set-based thinking deep into the dark acres of procedural approaches...

Please (for future questions): Do not paste pictures! Had to type my example in...

And btw: Your use my values here makes it difficult, to advise the correct thing. Depending on what you are doing there, a cursor might be needed actually. But in this case you should create the cursor from a query like I show you...

Try it like this:

DECLARE @xml XML=
'<roots>
  <root>
    <ID>5</ID>
    <LotResults>
      <ID>13</ID>
      <Result>
        <ID>5</ID>
        <Count>2</Count>
      </Result>
    </LotResults>
    <LotResults>
      <ID>13</ID>
      <Result>
        <ID>5</ID>
        <Count>2</Count>
      </Result>
    </LotResults>
    <StandardComment>
      <ID>0</ID>
    </StandardComment>
    <ReviewComment>
      <ID>0</ID>
    </ReviewComment>
  </root>
  <root>
    <ID>44</ID>
    <LotResults>
      <ID>444</ID>
      <Result>
        <ID>4444</ID>
        <Count>2</Count>
      </Result>
    </LotResults>
    <LotResults>
      <ID>555</ID>
      <Result>
        <ID>55</ID>
        <Count>2</Count>
      </Result>
    </LotResults>
    <StandardComment>
      <ID>5</ID>
    </StandardComment>
    <ReviewComment>
      <ID>5</ID>
    </ReviewComment>
  </root>
</roots>';

--and here's the query

SELECT r.value('ID[1]','int') AS root_ID
      ,lr.value('ID[1]','int') AS LotResult_ID
      ,lr.value('(Result/ID)[1]','int') AS LotResult_Result_ID
      ,lr.value('(Result/Count)[1]','int') AS LotResult_Result_Count
      ,r.value('(StandardComment/ID)[1]','int') AS StandardComment_ID 
      ,r.value('(ReviewComment/ID)[1]','int') AS ReviewComment_ID 
FROM @xml.nodes('/roots/root') AS A(r)
CROSS APPLY r.nodes('LotResults') AS B(lr)

这篇关于SQL Server XML文件具有多个名为相同的节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！