Parse XML with CDATA attribute using Oracle XML Features

Question

I have the following XML response after calling a WebService that has CDATA attribute.

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <RunQueryResponse xmlns="BlnSocket">
            <RunQueryResult>
                <![CDATA[<NewDataSet>
  <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="VFPData" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="VFPData">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="x1" type="xs:string" minOccurs="0" />
                <xs:element name="x2" type="xs:string" minOccurs="0" />
                <xs:element name="x3" type="xs:string" minOccurs="0" />
                <xs:element name="x4" type="xs:string" minOccurs="0" />
                <xs:element name="x5" type="xs:string" minOccurs="0" />
                <xs:element name="x6" type="xs:string" minOccurs="0" />
                <xs:element name="x7" type="xs:string" minOccurs="0" />
                <xs:element name="x7" type="xs:string" minOccurs="0" />
                <xs:element name="x9" type="xs:string" minOccurs="0" />
                <xs:element name="x10" type="xs:string" minOccurs="0" />
                <xs:element name="x11" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <VFPData>
    <x1>abc</x1>
    <x2>abc</x2>
    <x3>abc</x3>
    <x4>abc</x4>
    <x5>abc</x5>
    <x6>abc</x6>
    <x7>abc</x7>
    <x8>abc</x8>
    <x9>abc</x9>
    <x10>abc</x10>
    <x11>abc</x11>
  </VFPData>
</NewDataSet>]]>
            </RunQueryResult>
        </RunQueryResponse>
    </soap:Body>
</soap:Envelope>

I tried parsing it using the query below but it does not return any value.

Do I need to pay particular attention to the CDATA attribute? When the below query is run it does not return any data nor does it throw any error.

How should I change it so that I can query all the instances of VFPData data set?

SELECT xmlt.*
FROM XMLTABLE(XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' as "soap",
                            'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
                            'http://www.w3.org/2001/XMLSchema' as "xsd",
                            'http://www.w3.org/2001/XMLSchema' as "xs",
                            'urn:schemas-microsoft-com:xml-msdata' as "msdata"
                           ),'/soap:Envelope/soap:Body/RunQueryResponse/RunQueryResult/NewDataSet/VFPData'
                PASSING xmltype(
    '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <soap:Body>
          <RunQueryResponse xmlns="BlnSocket">
             <RunQueryResult><![CDATA[<NewDataSet>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="VFPData" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="VFPData">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="x1" type="xs:string" minOccurs="0" />
                    <xs:element name="x2" type="xs:string" minOccurs="0" />
                    <xs:element name="x3" type="xs:string" minOccurs="0" />
                    <xs:element name="x4" type="xs:string" minOccurs="0" />
                    <xs:element name="x5" type="xs:string" minOccurs="0" />
                    <xs:element name="x6" type="xs:string" minOccurs="0" />
                    <xs:element name="x7" type="xs:string" minOccurs="0" />
                    <xs:element name="x8" type="xs:string" minOccurs="0" />
                    <xs:element name="x9" type="xs:string" minOccurs="0" />
                    <xs:element name="x10" type="xs:string" minOccurs="0" />
                    <xs:element name="x11" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <VFPData>
        <x1>abc1</x1>
        <x3>abc1</x3>
        <x4>abc1</x4>
        <x5>abc1</x5>
        <x6>abc1</x6>
        <x9>abc1</x9>
        <x10>abc1</x10>
      </VFPData>
      <VFPData>
        <x1>abc2</x1>
        <x3>abc2</x3>
        <x4>abc2</x4>
        <x5>abc2</x5>
        <x6>abc2</x6>
        <x9>abc2</x9>
        <x10>abc2</x10>
      </VFPData>
    </NewDataSet>]]></RunQueryResult>
          </RunQueryResponse>
       </soap:Body>
    </soap:Envelope>')
COLUMNS x1 VARCHAR2(50) PATH 'x1') xmlt;

Expected output is to select data from VFPData section such as x1, x2 etc using the query in the 2nd code section on the post. — Ali Celebi
– Ali Celebi, Commented Jan 12, 2018 at 7:42

Arkadiusz Łukasiewicz · Accepted Answer · 2018-01-12 10:14:44Z

You have to combine two xmltable. In first xmltable you should extract cdata as clob and pass it to next xmltable. In 2nd xmltable you have to set up appropriate namespaces and do extract elements.

<RunQueryResponse xmlns="BlnSocket"> This part is change default namespace. Information about default namespace has to be included in namespace declaration. XMLNAMESPACES(default 'BlnSocket', ....

'http://www.w3.org/2001/XMLSchema' as "xs",'urn:schemas-microsoft-com:xml-msdata' as "msdata" - these two declaration are not needed in 1-st xmltable

/NewDataSet/VFPData/* - start will match any elements in VFPData (x1, x2 ,...)

SELECT zz.*
FROM XMLTABLE(XMLNAMESPACES(default 'BlnSocket', 
                             'http://www.w3.org/2003/05/soap-envelope' as "soap",
                            'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
                            'http://www.w3.org/2001/XMLSchema' as "xsd"

                           ),'/soap:Envelope/soap:Body/RunQueryResponse/RunQueryResult'


                PASSING xmltype(
    '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <soap:Body>
          <RunQueryResponse xmlns="BlnSocket">
             <RunQueryResult><![CDATA[<NewDataSet>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="VFPData" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="VFPData">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="x1" type="xs:string" minOccurs="0" />
                    <xs:element name="x2" type="xs:string" minOccurs="0" />
                    <xs:element name="x3" type="xs:string" minOccurs="0" />
                    <xs:element name="x4" type="xs:string" minOccurs="0" />
                    <xs:element name="x5" type="xs:string" minOccurs="0" />
                    <xs:element name="x6" type="xs:string" minOccurs="0" />
                    <xs:element name="x7" type="xs:string" minOccurs="0" />
                    <xs:element name="x8" type="xs:string" minOccurs="0" />
                    <xs:element name="x9" type="xs:string" minOccurs="0" />
                    <xs:element name="x10" type="xs:string" minOccurs="0" />
                    <xs:element name="x11" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <VFPData>
        <x1>abc1</x1>
        <x3>abc1</x3>
        <x4>abc1</x4>
        <x5>abc1</x5>
        <x6>abc1</x6>
        <x9>abc1</x9>
        <x10>abc1</x10>
      </VFPData>
      <VFPData>
        <x1>abc2</x1>
        <x3>abc2</x3>
        <x4>abc2</x4>
        <x5>abc2</x5>
        <x6>abc2</x6>
        <x9>abc2</x9>
        <x10>abc2</x10>
      </VFPData>
    </NewDataSet>]]></RunQueryResult>
          </RunQueryResponse>
       </soap:Body>
    </soap:Envelope>')
COLUMNS x1 clob PATH '.') xmlt
,xmltable (XMLNAMESPACES(default '', 
                            'http://www.w3.org/2001/XMLSchema' as "xs",
                            'urn:schemas-microsoft-com:xml-msdata' as "msdata"

                           ),'/NewDataSet/VFPData/*' passing  xmltype(xmlt.x1)

                           columns el_val varchar2(100)  path './text()'
                           , el_name varchar2(100) path './name()'

                           ) zz

How can I have the data in relation format e.g. field1 field2 value1 value2 Thanks

MT0 · Accepted Answer · 2018-01-12 10:18:49Z

You need to get the text of the CDATA and then parse that as XML and extract the values from that:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE data ( xml ) AS
SELECT xmltype(
    '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
       <soap:Body>
          <RunQueryResponse xmlns="BlnSocket">
             <RunQueryResult><![CDATA[<NewDataSet>
      <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="VFPData" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="VFPData">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="x1" type="xs:string" minOccurs="0" />
                    <xs:element name="x2" type="xs:string" minOccurs="0" />
                    <xs:element name="x3" type="xs:string" minOccurs="0" />
                    <xs:element name="x4" type="xs:string" minOccurs="0" />
                    <xs:element name="x5" type="xs:string" minOccurs="0" />
                    <xs:element name="x6" type="xs:string" minOccurs="0" />
                    <xs:element name="x7" type="xs:string" minOccurs="0" />
                    <xs:element name="x8" type="xs:string" minOccurs="0" />
                    <xs:element name="x9" type="xs:string" minOccurs="0" />
                    <xs:element name="x10" type="xs:string" minOccurs="0" />
                    <xs:element name="x11" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <VFPData>
        <x1>abc1</x1>
        <x3>abc1</x3>
        <x4>abc1</x4>
        <x5>abc1</x5>
        <x6>abc1</x6>
        <x9>abc1</x9>
        <x10>abc1</x10>
      </VFPData>
      <VFPData>
        <x1>abc2</x1>
        <x3>abc2</x3>
        <x4>abc2</x4>
        <x5>abc2</x5>
        <x6>abc2</x6>
        <x9>abc2</x9>
        <x10>abc2</x10>
      </VFPData>
    </NewDataSet>]]></RunQueryResult>
          </RunQueryResponse>
       </soap:Body>
    </soap:Envelope>') FROM DUAL;

Query 1:

SELECT c.*
FROM   data d
       CROSS JOIN
       XMLTABLE(
         XMLNAMESPACES(
           DEFAULT 'BlnSocket',
           'http://www.w3.org/2003/05/soap-envelope' as "soap",
           'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
           'http://www.w3.org/2001/XMLSchema' as "xsd"
         ),
         '/soap:Envelope/soap:Body/RunQueryResponse/RunQueryResult'
         PASSING d.xml    
         COLUMNS value CLOB PATH '/text()'
      ) x
      LEFT OUTER JOIN
      XMLTABLE(
         XMLNAMESPACES(
           'http://www.w3.org/2001/XMLSchema' as "xs",
           'urn:schemas-microsoft-com:xml-msdata' as "msdata"
         ),
         '/NewDataSet/VFPData'
         PASSING XMLType( x.value )
         COLUMNS x1  VARCHAR2(5) PATH 'x1/text()',
                 x3  VARCHAR2(5) PATH 'x3/text()',
                 x4  VARCHAR2(5) PATH 'x4/text()',
                 x5  VARCHAR2(5) PATH 'x5/text()',
                 x6  VARCHAR2(5) PATH 'x6/text()',
                 x9  VARCHAR2(5) PATH 'x9/text()',
                 x10 VARCHAR2(5) PATH 'x10/text()'
      ) c
      ON ( 1 = 1 )

Results:

|   X1 |   X3 |   X4 |   X5 |   X6 |   X9 |  X10 |
|------|------|------|------|------|------|------|
| abc1 | abc1 | abc1 | abc1 | abc1 | abc1 | abc1 |
| abc2 | abc2 | abc2 | abc2 | abc2 | abc2 | abc2 |

Collectives™ on Stack Overflow

Parse XML with CDATA attribute using Oracle XML Features

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related