MSSQL解析嵌套XML

Question

我有一条XML消息，我需要使用存储过程将测试信息从表格中并进入表中。

我一直在使用此查询：

select distinct 
    'N' as ORIGSTS, 
    doc1.Samples.value('(ID)[1]', 'nvarchar(20)') as 'SAMPLE_ID', 
    doc2.Tests.value('(Name)[1]', 'nvarchar(20)') as 'TEST_NAME' 
from
    @messageXml.nodes('/CDFAOrderMsg/Samples/Sample') as doc1(Samples), 
    @messageXml.nodes('/CDFAOrderMsg/Samples/Sample/Tests/Test') as doc2(Tests)
where doc1.Samples.value('(ID)[1]', 'nvarchar(20)') = 456
order by 2, 3

问题在于它将返回样本ID 456以及消息中列出的所有测试。我需要能够提取测试名称及其关联的样品ID以插入表中。当前，有两个样本和三个测试，每个测试只能返回12行6.

如何使其返回所有样本及其各自的测试名称的列表？

谢谢，

斯科特

<OrderMsg xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Samples>
    <SourceType>Non-Animal</SourceType>
    <Sample>
      <ID>456</ID>
      <Tests>
        <Test>
          <Name>SPC</Name>
        </Test>
        <Test>
          <Name>COL</Name>
        </Test>
        <Test>
          <Name>ANTI</Name>
        </Test>
      </Tests>
    </Sample>
    <Sample>
      <ID>457</ID>
      <Tests>
        <Test>
          <Name>HPC</Name>
        </Test>
        <Test>
          <Name>DEL</Name>
        </Test>
        <Test>
          <Name>NVT</Name>
        </Test>
      </Tests>
    </Sample>
  </Samples>
</OrderMsg>

Answer 1

这是一个查询，可以使用外部应用功能获得预期的结果，以获取子节点收集。

DECLARE @x xml
SET @x = '<OrderMsg xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Samples>
        <SourceType>Non-Animal</SourceType>
        <Sample>
          <ID>456</ID>
          <Tests>
            <Test>
              <Name>SPC</Name>
            </Test>
            <Test>
              <Name>COL</Name>
            </Test>
            <Test>
              <Name>ANTI</Name>
            </Test>
          </Tests>
        </Sample>
        <Sample>
          <ID>457</ID>
          <Tests>
            <Test>
              <Name>HPC</Name>
            </Test>
            <Test>
              <Name>DEL</Name>
            </Test>
            <Test>
              <Name>NVT</Name>
            </Test>
          </Tests>
        </Sample>
      </Samples>
    </OrderMsg>'
SELECT DISTINCT
  'N' AS ORIGSTS,
  s.sampleNode.query('ID').value('.', 'nvarchar(20)') AS 'SAMPLE_ID',
  t.testNode.query('Test/Name').value('.', 'nvarchar(20)') AS 'TEST_NAME'
FROM @x.nodes('//Samples/Sample') s (sampleNode)
OUTER APPLY (SELECT
  x.testNode.query('.') testNode
FROM sampleNode.nodes('Tests/Test') x (testNode)) t
WHERE s.sampleNode.value('(ID)[1]', 'nvarchar(20)') = 456
ORDER BY 2, 3