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

有一个正确的，简单易懂的答案：

您的XML：

DECLARE @XMLResult XML=
N'<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>';

-正确的答案已经由@bdebaere提供。如果您使用它，请在此处设置接受（但是您当然可以投票;-)）。
-但是您可以使用一个名称空间声明来清楚地说明所有这些：
-优点：如果您多次调用XML方法，则必须一遍又一遍地重复声明。

WITH XMLNAMESPACES(DEFAULT 'http://testurl.com/'
                          ,'http://schemas.xmlsoap.org/soap/envelope/' AS [soap])
SELECT @XMLResult.query('/soap:Envelope/soap:Body/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results');

-轻松奶酪方法使用深度搜索和名称空间通配符
-一般建议是：尽可能具体，但有时懒惰者会赢...

SELECT @XMLResult.query('//*:Results') 

-完整的答案是这样的：

WITH XMLNAMESPACES(DEFAULT 'http://testurl.com/'
                          ,'http://schemas.xmlsoap.org/soap/envelope/' AS [soap])
SELECT dd.value('(Title/text())[1]','nvarchar(max)') AS Title
      ,dd.value('(Forename1/text())[1]','nvarchar(max)') AS Forename1
      ,dd.value('(Forename2/text())[1]','nvarchar(max)') AS Forename2
      ,dd.value('(Forename3/text())[1]','nvarchar(max)') AS Forename3
      ,dd.value('(Sex/text())[1]','nvarchar(1)') AS Sex
      ,dd.value('(DateOfBirth/text())[1]','nvarchar(max)') AS DateOfBirth --Hint: don't use 'datetime' here. Rather pull this data as string and use CONVERT with the appropriate style hint
      ,dd.value('(Surname/text())[1]','nvarchar(max)') AS Surname
FROM @XMLResult.nodes('/soap:Envelope/soap:Body/HCNSearchResponse/HCNSearchResult/HCNLookupResult/Results/DemographicDetails') A(dd);

结果

+-------+-----------+-----------+-----------+-----+-------------+---------+
| Title | Forename1 | Forename2 | Forename3 | Sex | DateOfBirth | Surname |
+-------+-----------+-----------+-----------+-----+-------------+---------+
| Ms    | F1 test   | NULL      | F3 test   | F   | 01/01/2000  | test1   |
+-------+-----------+-----------+-----------+-----+-------------+---------+
| Mr    | F1 test   | NULL      | NULL      | M   | 01/01/2000  | test2   |
+-------+-----------+-----------+-----------+-----+-------------+---------+