CODEWITHSUNDEEP
sqlsql-servert-sql
ailinmcc666
ailinmcc666

Reputation: 413

How do I get the specified node from XML in SQL

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'

Upvotes: 3

Views: 182

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 2

Vqf5mG96cSTT
Vqf5mG96cSTT

Reputation: 2891

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

Upvotes: 3

Related Questions