6 Replies Latest reply: Oct 17, 2012 5:19 PM by Jason_(A_Non) RSS

    Querying attributes from XML

    964683
      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
          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
            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)
              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
                >
                - 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
                  >
                  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)
                    Apparently I forgot to update the XML before running that test case then. Thanks for the catch and clarification.