xml database oracle-database oracle-xml-db oracle-xml-db-repository

Parse XML with CDATA attribute using Oracle XML Features

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;

Solution

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