CODEWITHSUNDEEP
sqlxmlxpath
Rohit.Deepak
Rohit.Deepak

Reputation: 23

How to parse xml having multiple levels of many to one relationships using Xpath

I am trying to figure out a way to traverse multi-level many to one relationships in xml using xpath(a way to flatten the xml). Please look at example below for details:

<?xml version="1.0" encoding="UTF-8"?>
  <Sale>
    <CustomerDetail>
      <CustomerCode><![[E]]></CustomerCode>
      <CustRep>
        <phoneNumber><![[000 000 0000]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![[000 000 0000]]></phoneNumber>
      </CustRep>
    </CustomerDetail>
    <CustomerDetail>
      <CustomerCode><![[X]]></CustomerCode>
      <CustRep>
        <phoneNumber><![[000 000 0000]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![[000 000 0000]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![[000 000 0000]]></phoneNumber>
      </CustRep>
    </CustomerDetail>
  </Sale>

As shown in the example of a Sale can have many CustomerDetails and a CustomerDetail can have many CustReps. How to flatten this xml structure into a table using xpath? Any help in this regard is highly appreciated?

The desired output as shown below:

CustomerCode    Phone Number
E   000 000 0000
E   000 000 0000
X   000 000 0000
X   000 000 0000
X   000 000 0000

Upvotes: 0

Views: 171

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Thx for the edit with the expected output, but you missed to state your RDBMS. Reading XML is highly vendor specific! The following is SQL-Server-syntax:

Remarks

  • Your XML is not valid. The CDATA sections are not correct. Best was to use proper escaping and get rid of the CDATA sections.
  • I added a running number to your phone numbers to see their output more clearly

Try this:

DECLARE @xml XML=
'<?xml version="1.0" encoding="UTF-8"?>
  <Sale>
    <CustomerDetail>
      <CustomerCode><![CDATA[E]]></CustomerCode>
      <CustRep>
        <phoneNumber><![CDATA[000 000 0001]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![CDATA[000 000 0002]]></phoneNumber>
      </CustRep>
    </CustomerDetail>
    <CustomerDetail>
      <CustomerCode><![CDATA[X]]></CustomerCode>
      <CustRep>
        <phoneNumber><![CDATA[000 000 0003]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![CDATA[000 000 0004]]></phoneNumber>
      </CustRep>
      <CustRep>
        <phoneNumber><![CDATA[000 000 0005]]></phoneNumber>
      </CustRep>
    </CustomerDetail>
  </Sale>';

  SELECT cd.value('(CustomerCode/text())[1]','nvarchar(max)') AS CustomerCode
        ,cr.value('(phoneNumber/text())[1]','nvarchar(max)') AS phoneNumber
  FROM @xml.nodes('/Sale/CustomerDetail') AS A(cd)
  CROSS APPLY cd.nodes('CustRep') AS B(cr);

The result

E   000 000 0001
E   000 000 0002
X   000 000 0003
X   000 000 0004
X   000 000 0005

Upvotes: 1

Related Questions