2 Replies Latest reply: Oct 30, 2012 10:16 AM by 952738 RSS

    Element Names Query Issue

    952738
      Should be simple select with extractvalue to get the attribute value but having issues when hardcoding the element path.

      My question is why does it work with the wildcard?

      Query works with:

      Select Extractvalue(Xml_Document, '/*/@GBRON') Gbron
      From Xml_Test E
      Where Existsnode (Xml_Document, '/*') > 0
      and e.audit_id = 1;

      But not:

      Select Extractvalue(Xml_Document, '/OPS/@GBRON') Gbron
      From Xml_Test E
      Where Existsnode (Xml_Document, '/OPS/) > 0
      and e.audit_id = 1;

      I checked the root element node with:

      Select Distinct Xmltype(Extract(Value(Xml_Tab),'/').Getstringval()).Getrootelement() Deptype
      From xml_test E, Table(Xmlsequence(Extract(E.Xml_Document,'/*'))) Xml_Tab
      Where Existsnode(E.Xml_Document, '/*')>0
      And E.Audit_Id = 1;

      and it returns OPS.

      Example I have been using is:

      Create Table Xml_Test (Audit_Id Integer, Xml_Document Xmltype);

      Insert Into Xml_Test(Audit_Id, Xml_Document)
      values(1,'<?xml version="1.0" encoding="ISO-8859-1"?><!--Sample XML file generated by XMLSpy v2010 rel. 3 sp1 (http://www.altova.com)-->
      <ers:OPS GBRSVN="a" UKELSS="a" GBRSRN="a" FR="LVA" DATI="2001-12-17T09:30:00.000000" GBRON="HUBFCC20120927000028" UKXSD="a" AD="DNK" xsi:schemaLocation="http://www.test/schema/ers/v3 http://localhost:8080/public/elogs_1_3/xsd/ers_3_1_beta.xsd" xmlns:ers="http://www.test/schema/ers/v3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <ers:DAT><ers:ERS DATI="2001-12-17T09:30:00.000000" GBRRN="HUB000000000000000"></ers:ERS></ers:DAT></ers:OPS>');
        • 1. Re: Element Names Query Issue
          odie_63
          My question is why does it work with the wildcard?
          Because there's a namespace but you didn't declare it :
          SQL> Select Extractvalue(Xml_Document, '/OPS/@GBRON', 'xmlns="http://www.test/schema/ers/v3"') Gbron
            2  From Xml_Test E
            3  Where Existsnode (Xml_Document, '/OPS', 'xmlns="http://www.test/schema/ers/v3"') > 0
            4  and e.audit_id = 1;
           
          GBRON
          --------------------------------------------------------------------------------
          HUBFCC20120927000028
           
          XMLSequence has been deprecated in recent releases, so check out XMLTable function (if available in your version) :
          SQL> SELECT x.*
            2  FROM xml_test t
            3     , XMLTable(
            4         XMLNamespaces(default 'http://www.test/schema/ers/v3')
            5       , '/OPS'
            6         passing t.xml_document
            7         columns gbron varchar2(30) path '@GBRON'
            8       ) x
            9  ;
           
          GBRON
          ------------------------------
          HUBFCC20120927000028
           
          • 2. Re: Element Names Query Issue
            952738
            Thank you - so obviously when you know how.