3 Replies Latest reply: Oct 9, 2012 2:50 AM by odie_63 RSS

    XMLQuery always returns null

    user10737970
      I have the following XML in a table called IMAGE in a column called METAXMP:

      <xmpMetadata xmlns="http://xmlns.oracle.com/ord/meta/xmp" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/xmp http://xmlns.oracle.com/ord/meta/xmp" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
      <rdf:Description about="" xmlns:dc="http://purl.org/dc/elements/1.1/">
      <dc:name>RAVEN HAIRED BUBBLECUT BARBIE vintage repro GORGEOUS </dc:name>
      <dc:type>Online Purchase</dc:type>
      <dc:purchaseDate>11-JUL-09</dc:purchaseDate>
      <dc:purchasePrice>14.99</dc:purchasePrice>
      <dc:webSite>Ebay.com</dc:webSite>
      <dc:shippingPrice>5.5</dc:shippingPrice>
      <dc:ebayItemId>130317756757</dc:ebayItemId>
      </rdf:Description>
      </rdf:RDF>
      </xmpMetadata>

      I am trying to extract the value of the 'name' node with:

      SELECT xmlquery('/xmpMetadata/RDF/Description/name' PASSING metaxmp RETURNING CONTENT)
      FROM image;

      The returned value is always null. Is there something I'm missing here? How can I extract the name node value?

      Thanks.
        • 1. Re: XMLQuery always returns null
          odie_63
          The returned value is always null. Is there something I'm missing here?
          Namespaces declaration :
          SQL> SELECT XMLCast(
            2    XMLQuery(
            3     'declare default element namespace "http://xmlns.oracle.com/ord/meta/xmp"; (: :)
            4      declare namespace rdf = "http://www.w3.org/1999/02/22-rdf-syntax-ns#"; (: :)
            5      declare namespace dc = "http://purl.org/dc/elements/1.1/"; (: :)
            6      /xmpMetadata/rdf:RDF/rdf:Description/dc:name'
            7      PASSING metaxmp RETURNING CONTENT)
            8    as varchar2(80)
            9    ) as node_value
           10  FROM image;
           
          NODE_VALUE
          --------------------------------------------------------------------------------
          RAVEN HAIRED BUBBLECUT BARBIE vintage repro *GORGEOUS *
           
          • 2. Re: XMLQuery always returns null
            user10737970
            Thanks for the solution! What's the purpose or meaning of the (: :) in the declarations?
            • 3. Re: XMLQuery always returns null
              odie_63
              In the XQuery language, (: and &#x3a;) are comment delimiters.

              I used them in this specific situation because otherwise my SQL client tool (SQL*Plus) would interpret an end-of-line semicolon as the end of the statement.

              http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#autoId23