XML data to SQL tables, difficulty gettting child node data

Question

I have an xml file that I am loading into SQL. I have no problem getting the xml file loaded and the top level nodes into a sql table. I am having difficulty reaching the next level child node.

Here is an example xml file (similar to what I am trying to get into tables):

    <RSR:ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:RSR="urn:rsrNamespace" xsi:schemaLocation="urn:rsrNamespace SomeSchema.xsd">
      <ClientReport>
        <ClientID>05929</ClientID>
        <ClientFirstName>Eric</ClientFirstName>
        <ClientLastName>Jones</ClientLastName>
        <ClientService>
          <ClientServices>
            <ServiceID>250</ServiceID>
            <Salesperson>Charlie</Salesperson>
            <Cost>15.95</Cost>
          </ClientServices>
          <ClientServices>
            <ServiceID>356</ServiceID>
            <Salesperson>Jane</Salesperson>
            <Cost>34.25</Cost>
          </ClientServices>
        </ClientService>
      </ClientReport>
    </RSR:ROOT>

I have no problem getting the top level nodes, ClientID and ClientName, into a table. I can run this sql and get values into a temp table CLIENTREPORT:

CREATE TABLE ##XMLTable (XMLData XML)

SET @cmd = 'INSERT INTO ##XMLTable SELECT CONVERT(XML, BulkColumn) AS Bulkcolumn FROM OPENROWSET (BULK ''' + @FullPathName + ''', SINGLE_BLOB) as X'

SET @cmd = 'WITH XMLNAMESPACES (''urn:rsrNamespace'' AS p) 
INSERT INTO ##CLIENTREPORT (ClientID, ClientFullName)
SELECT
    CLIENTDATA.ClientReport.value(''(./ClientID)[1]'', ''VARCHAR(100)''),
    CLIENTDATA.ClientReport.value(''(./ClientFirstName)[1]'', ''VARCHAR(100)'') + '' '' + CLIENTDATA.ClientReport.value(''(./ClientLastName)[1]'', ''VARCHAR(100)'')
FROM ##XMLTable X
    CROSS APPLY X.XMLData.nodes(''p:ROOT/ClientReport'') AS CLIENTDATA (ClientReport)'

I am having an issue getting to those lower level nodes - ServiceID, Salesperson, Cost. Here is what I have that does not work:

SET @cmd = 'WITH XMLNAMESPACES (''urn:rsrNamespace'' AS p) 
INSERT INTO ##CLIENTREPORTSERVICES (ClientID, Salesperson)
SELECT
    CLIENTDATA.ClientReport.value(''(./ClientID)[1]'', ''VARCHAR(100)''),
    CLIENTSERVICEDATA.ClientServices.value(''(./Salesperson)[1]'', ''VARCHAR(25)'')
FROM ##XMLTable X
    CROSS APPLY X.XMLData.nodes(''p:ROOT/ClientReport'') AS CLIENTDATA (ClientReport)
    CROSS APPLY CLIENTDATA.ClientReport.nodes(''ClientService/ClientServices'') AS CLIENTSERVICEDATA (ClientServices)'

All I get are null values in the Salesperson field. What am I missing about XQuery and navigating to lower level nodes?

Answer 1

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (XMLData XML);
INSERT @tbl (XMLData) VALUES
(N'<QSR:ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:QSR="urn:qsrNamespace" xsi:schemaLocation="urn:qsrNamespace SomeSchema.xsd">
    <ClientReport>
        <ClientID>05929</ClientID>
        <ClientFirstName>Eric</ClientFirstName>
        <ClientLastName>Jones</ClientLastName>
        <ClientService>
            <ClientServices>
                <ServiceID>250</ServiceID>
                <Salesperson>Charlie</Salesperson>
                <Cost>15.95</Cost>
            </ClientServices>
            <ClientServices>
                <ServiceID>356</ServiceID>
                <Salesperson>Jane</Salesperson>
                <Cost>34.25</Cost>
            </ClientServices>
        </ClientService>
    </ClientReport>
</QSR:ROOT>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES ('urn:qsrNamespace' AS QSR)
SELECT c.value('(ClientID/text())[1]', 'VARCHAR(20)') AS ClientID
    , c.value('(ClientFirstName/text())[1]', 'VARCHAR(20)') AS ClientFirstName
    , c.value('(ClientLastName/text())[1]', 'VARCHAR(20)') AS ClientLastName
FROM @tbl
CROSS APPLY XMLData.nodes('/QSR:ROOT/ClientReport') AS t(c);

;WITH XMLNAMESPACES ('urn:qsrNamespace' AS QSR)
SELECT p.value('(ClientID/text())[1]', 'VARCHAR(20)') AS ClientID
    , c.value('(ServiceID/text())[1]', 'VARCHAR(20)') AS ServiceID
    , c.value('(Salesperson/text())[1]', 'VARCHAR(20)') AS Salesperson
    , c.value('(Cost/text())[1]', 'DECIMAL(10,2)') AS Cost
FROM @tbl
CROSS APPLY XMLData.nodes('/QSR:ROOT/ClientReport') AS t1(p)
CROSS APPLY p.nodes('ClientService/ClientServices') AS t2(c);

Output #1

ClientID	ClientFirstName	ClientLastName
05929	Eric	Jones

Output #2

ClientID	ServiceID	Salesperson	Cost
05929	250	Charlie	15.95
05929	356	Jane	34.25