sql - 如何在SQL中从XML中获取指定的节点

Question

我正在尝试从 XML 数据中获取特定节点，但我终其一生都无法让它发挥作用。我在变量和表中有以下 XML(两者中的数据相同):

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <HCNSearchResponse xmlns="http://testurl.com/">
      <HCNSearchResult>
        <HCNLookupResult>
          <MsgID>test1</MsgID>
          <Results>
            <DemographicDetails>
              <Title>Ms</Title>
              <Forename1>F1 test</Forename1>
              <Forename2 />
              <Forename3>F3 test</Forename3>
              <Sex>F</Sex>
              <DateOfBirth>01/01/2000</DateOfBirth>
              <Surname>test1</Surname>
            </DemographicDetails>
            <DemographicDetails>
              <Title>Mr</Title>
              <Forename1>F1 test</Forename1>
              <Forename2 />
              <Forename3></Forename3>
              <Sex>M</Sex>
              <DateOfBirth>01/01/2000</DateOfBirth>
              <Surname>test2</Surname>
            </DemographicDetails>
          </Results>
        </HCNLookupResult>
      </HCNSearchResult>
    </HCNSearchResponse>
  </soap:Body>
</soap:Envelope>

我试图从中提取结果节点，但我无法让它工作，我已经尝试了所有这些:

    SELECT @XMLResult.query('declare namespace 
    ns="http://testurl.com/";
    /ns:HCNSearchResponse/ns:HCNSearchResult/ns:HCNLookupResult/ns:Results')

    SELECT @XMLResult.query('/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results')

    SELECT T.N.query('.')
    FROM @XMLResult.nodes('/root/Body/Envelope/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results') as T(N)

    SELECT x.*, y.c.query('.')
    FROM #xml x
    CROSS APPLY x.resultsXML.nodes('/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results') y(c)

这些都没有完成任务!其中每个的 select 语句只会返回一个空结果。我有一个名为 #XML 的表，其中只有一行，其中 XML 存储在名为“resultsXML”的列中，相同的数据存储在名为 @XMLResult 的变量中。有谁能帮我解决这个问题吗？

如果有任何区别，这就是我在 xml 表上填充该列的方式:

INSERT #XML ( resultsXML )
EXEC sp_OAGetProperty @Obj, 'responseXML.XML'

Answer 1

您的第一个查询非常接近，但您必须声明使用的所有命名空间。您忘记声明肥皂的 namespace 。请参阅下面的工作查询。

DECLARE @xmlTable TABLE ([Value] XML)
INSERT INTO
    @xmlTable
VALUES
    ('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <HCNSearchResponse xmlns="http://testurl.com/">
      <HCNSearchResult>
        <HCNLookupResult>
          <MsgID>test1</MsgID>
          <Results>
            <DemographicDetails>
              <Title>Ms</Title>
              <Forename1>F1 test</Forename1>
              <Forename2 />
              <Forename3>F3 test</Forename3>
              <Sex>F</Sex>
              <DateOfBirth>01/01/2000</DateOfBirth>
              <Surname>test1</Surname>
            </DemographicDetails>
            <DemographicDetails>
              <Title>Mr</Title>
              <Forename1>F1 test</Forename1>
              <Forename2 />
              <Forename3></Forename3>
              <Sex>M</Sex>
              <DateOfBirth>01/01/2000</DateOfBirth>
              <Surname>test2</Surname>
            </DemographicDetails>
          </Results>
        </HCNLookupResult>
      </HCNSearchResult>
    </HCNSearchResponse>
  </soap:Body>
</soap:Envelope>')

SELECT
    [Value].query(
        'declare namespace soap = "http://schemas.xmlsoap.org/soap/envelope/"
        ;declare default element namespace "http://testurl.com/"
        ;/soap:Envelope/soap:Body/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results')
FROM
    @xmlTable