Forum Stats

  • 3,815,621 Users
  • 2,259,062 Discussions
  • 7,893,186 Comments

Discussions

Categories

Parse data from XML

HaniYS
HaniYS Member Posts: 981 Bronze Badge
edited Jun 8, 2015 7:41AM in SQL & PL/SQL

Hi

DB version 11.2.0.1

I have clob with name "a" in table dummy_clob which include XML date, i need to select this data but there is error apper ORA-19228: XPST0008

Query:

SELECT x.Cba_Number,x.Reference_Id
  FROM dummy_clob t,
       XMLTABLE (
           '/ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1"'
           PASSING xmltype (t.a)
           COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',
                   Reference_Id VARCHAR2 (2000) PATH 'ReferenceId') x

XML:

<ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1">
    <CbaNumber>29291618</CbaNumber>
    <ReferenceId>123459</ReferenceId>
</ns2:SearchResponseType>
· Share on TwitterShare on Facebook

Best Answer

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Jun 8, 2015 7:41AM Answer ✓

    You have to add the namespace declarations to the xmltable function at first:

    SELECT x.Cba_Number,x.Reference_Id

    FROM t,

    XMLTABLE (

              xmlnamespaces ('##default' as "ns2", default 'urn:com.xxx:xxx:xxx:1')

              ,'/ns2:SearchResponseType'

              PASSING xmltype (t.a)

              COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',

                      Reference_Id VARCHAR2 (2000) PATH 'ReferenceId'

                      ) x

    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,328 Red Diamond
    edited Jun 8, 2015 7:32AM

    You have to use XMLNAMESPACES clause of XMLTABLE:


    WITH dummy_clob AS (

                        SELECT '<ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1">

    <CbaNumber>29291618</CbaNumber>

    <ReferenceId>123459</ReferenceId>

    </ns2:SearchResponseType>' a from dual)

    SELECT x.Cba_Number,x.Reference_Id

      FROM dummy_clob t,

           XMLTABLE (

               XMLNAMESPACES('##default' as "ns2",

                             default 'urn:com.xxx:xxx:xxx:1'

                            ),

               '/ns2:SearchResponseType'

               PASSING xmltype (t.a)

               COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',

                 Reference_Id VARCHAR2 (2000) PATH 'ReferenceId') x

    /


    CBA_NUMBER REFERENCE_ID
    ---------- ------------
    29291618   123459

    SQL>

    SY.

    HaniYS
    · Share on TwitterShare on Facebook
  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Jun 8, 2015 7:41AM Answer ✓

    You have to add the namespace declarations to the xmltable function at first:

    SELECT x.Cba_Number,x.Reference_Id

    FROM t,

    XMLTABLE (

              xmlnamespaces ('##default' as "ns2", default 'urn:com.xxx:xxx:xxx:1')

              ,'/ns2:SearchResponseType'

              PASSING xmltype (t.a)

              COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',

                      Reference_Id VARCHAR2 (2000) PATH 'ReferenceId'

                      ) x

    · Share on TwitterShare on Facebook
This discussion has been closed.