How do I get the specified node from XML in SQL

Question

I'm trying to get a specific node from XML data, and I can't for the life of me get it to work. I have the following XML in a variable and a table (same data in both):

<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>

I'm trying to extract just the Results node from this, and I can't get it to work, I've tried all of these:

    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)

None of these have done the job! The select statement for each of these just brings back an empty result. I have a table called #XML with a single row where the XML is stored in a column called "resultsXML", and the same data in a variable called @XMLResult. Is anyone able to help me with this?

If it makes any difference, this is how I populate that column on the xml table:

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

Answer 1

You were very close with your first query but you have to declare all namespaces used. You forgot to declare the namespace for soap. See the working query below.

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

Answer 2

There is a correct, an easy-cheese and a fully blown answer:

Your 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>';

--the correct answer is provided by @bdebaere already. If you go with it, please set the acceptance there (but you may upvote of course ;-) ).
--But you could articulate the same with one namespace declaration for all:
--The advantage: If you used several calls to XML-methods, you'd have to repeat the declarations over and over otherwise...

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');

--the easy-cheese approach uses the deep search and a namespace wild-card
--The general advise is: be as specific as possible, but sometimes the lazy ones win...

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

--and the fully blown answer was this:

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);

The result

+-------+-----------+-----------+-----------+-----+-------------+---------+
| 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   |
+-------+-----------+-----------+-----------+-----+-------------+---------+