使用Oracle XML Features分析带有CDATA属性的XML

23c0lvtd 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(121)

在调用具有CDATA属性的WebService后，我得到了以下XML响应。

<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>

我尝试使用下面的查询来解析它，但它没有返回任何值。
我需要特别注意CDATA属性吗？当下面的查询运行时，它不会返回任何数据，也不会抛出任何错误。
我应该如何更改它，以便我可以查询VFPData数据集的所有示例？

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;

oracle

来源：https://stackoverflow.com/questions/48221454/parse-xml-with-cdata-attribute-using-oracle-xml-features

2条答案

按热度按时间

nhaq1z211#

你必须合并两个xmltable。在第一个xmltable中，你应该提取cdata作为clob并将其传递给下一个xmltable。在第二个xmltable中，你必须设置适当的命名空间并提取元素。
<RunQueryResponse xmlns="BlnSocket">此部分是更改默认命名空间。有关默认命名空间的信息必须包含在命名空间声明中。XMLNAMESPACES(default 'BlnSocket', ....
'http://www.w3.org/2001/XMLSchema' as "xs",'urn:schemas-microsoft-com:xml-msdata' as "msdata"-第一个xmltable中不需要这两个声明
/NewDataSet/VFPData/*- start将匹配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

赞(0）回复(0）举报 2023-10-16

lyfkaqu12#

您需要获取CDATA的文本，然后将其解析为XML并从中提取值：
SQL Fiddle

Oracle 11g R2架构设置：

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;

查询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 )

结果：

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

赞(0）回复(0）举报 2023-10-16

我来回答

使用Oracle XML Features分析带有CDATA属性的XML

2条答案

相关问题

热门标签

最新问答