This discussion is archived
6 Replies Latest reply: Oct 17, 2012 3:19 PM by Jason_(A_Non) RSS

Querying attributes from XML

964683 Newbie
Currently Being Moderated
I have Oracle 10g . In a table with just one record there is a a clob with the following xml.

<container>
<param name="paramA" value="valueA" />
<param name="paramB" value="valueB" />
...
</container>

How can I enumerate paramA, paramB from above?
  • 1. Re: Querying attributes from XML
    odie_63 Guru
    Currently Being Moderated
    CLOB storage is kinda out of the scope of XML DB.
    Depending on the size of the XML, you would certainly benefit from migrating the column to XMLType datatype (Object-Relational storage if you can).

    With your current settings, you can do something like this :
    SQL> create table test_xml (xmlcontent clob);
     
    Table created
     
    SQL> 
    SQL> insert into test_xml (xmlcontent)
      2  values ('<container>
      3  <param name="paramA" value="valueA" />
      4  <param name="paramB" value="valueB" />
      5  <param name="paramC" value="valueC" />
      6  </container>');
     
    1 row inserted
     
    SQL> 
    SQL> select x.paramName, x.paramValue
      2  from test_xml t
      3     , xmltable('/container/param'
      4         passing xmltype(t.xmlcontent)
      5         columns paramName  varchar2(30) path '@name'
      6               , paramValue varchar2(80) path '@value'
      7       ) x
      8  ;
     
    PARAMNAME                      PARAMVALUE
    ------------------------------ --------------------------------------------------------------------------------
    paramA                         valueA
    paramB                         valueB
    paramC                         valueC
     
    (assuming release 10.2)
  • 2. Re: Querying attributes from XML
    964683 Newbie
    Currently Being Moderated
    Thanks..

    I have 2 questions:
    - If I move to XMLType.. would it change the way I am writing the query? Any other concrete benifits?

    - Actually my real xml is as follows:

    <container>
    <param xsi:type="paramA" value="valueA" />
    <param xsi:type="paramB" value="valueB" />
    ...
    </container>

    When I use it as follows I am not able to get the values:


    SQL> select x.paramName, x.paramValue
    2 from test_xml t
    3 , xmltable('/container/param'
    4 passing xmltype(t.xmlcontent)
    5 columns paramName varchar2(30) path '@xsi:type'
    6 , paramValue varchar2(80) path '@value'
    7 ) x
    8 ;
  • 3. Re: Querying attributes from XML
    Jason_(A_Non) Expert
    Currently Being Moderated
    Your query neglected to show the error message you were getting, which should have been
    select x.paramName, x.paramValue
      from test_xml t
         , xmltable('/container/param'
                    passing xmltype(t.xmlcontent)
                    columns
                    paramName varchar2(30) path '@type'
                  , paramValue varchar2(80) path '@value') x
     
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00234: namespace prefix "xsi" is not declared
    Error at line 2
    ORA-06512: at "SYS.XMLTYPE", line 254
    ORA-06512: at line 1
    Taking that, you would ask yourself how to declare the namespace and some searching would eventually turn up that
    1) your sample XML is invalid as it does not have a namespace prefix defined for xsi
    2) You need to use XMLNamespaces within the [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm#SQLRF06232]XMLTable function

    Putting those together and assuming you mean the standard URI for the xsi prefix, your sample XML should look like
    <container xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <param xsi:type="paramA" value="valueA"/>
         <param xsi:type="paramB" value="valueB"/>
    </container>
    and the query would look like
    select x.paramName, x.paramValue
      from test_xml t
         , xmltable(XMLNamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi"),
                   '/container/param'
                    passing xmltype(t.xmlcontent)
                    columns 
                    paramName varchar2(30) path '@xsi:type'
                  , paramValue varchar2(80) path '@value') x;
  • 4. Re: Querying attributes from XML
    odie_63 Guru
    Currently Being Moderated
    >
    - If I move to XMLType.. would it change the way I am writing the query? Any other concrete benifits?
    >
    The query would be the same except that you wouldn't have to build an XMLType "on-the-fly" from the CLOB.
    That could lead to significant performance gain if the XML document is large.

    What's the best to do depends on your use case actually.
    If your goal is to access large volumes of XML data relationally, you can use an XML schema and store documents as Object-Relational.

    See the XML DB Dev Guide :
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/toc.htm
  • 5. Re: Querying attributes from XML
    odie_63 Guru
    Currently Being Moderated
    >
    2) You need to use XMLNamespaces within the [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm#SQLRF06232]XMLTable function
    >

    Actually, xsi is one of the XQuery predefined prefixes, we don't have to declare it :
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb_xquery.htm#sthref1716
    SQL> select x.paramName, x.paramValue
      2  from test_xml t
      3     , xmltable('/container/param'
      4         passing xmltype(t.xmlcontent)
      5         columns paramName  varchar2(30) path '@xsi:type'
      6               , paramValue varchar2(80) path '@value'
      7       ) x
      8  ;
     
    PARAMNAME                      PARAMVALUE
    ------------------------------ --------------------------------------------------------------------------------
    paramA                         valueA
    paramB                         valueB
    paramC                         valueC
     
    It was worth mentioning the XMLNamespaces syntax nonetheless ;)
  • 6. Re: Querying attributes from XML
    Jason_(A_Non) Expert
    Currently Being Moderated
    Apparently I forgot to update the XML before running that test case then. Thanks for the catch and clarification.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points