Parse XML with CDATA attribute using Oracle XML Features

Question

Parse XML with CDATA attribute using Oracle XML Features

1.5k Views Asked by Ali Celebi At 20 October 2024 at 22:07

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;

Original Q&A

There are 2 best solutions below

MT0 On 12 January 2018 at 10:18

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 |

**Arkadiusz Łukasiewicz** · Accepted Answer

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

Parse XML with CDATA attribute using Oracle XML Features

There are 2 best solutions below

Related Questions in XML

Related Questions in DATABASE

Related Questions in ORACLE

Related Questions in ORACLE-XML-DB

Related Questions in ORACLE-XML-DB-REPOSITORY

Trending Questions

Popular # Hahtags

Popular Questions