This discussion is archived
2 Replies Latest reply: Oct 30, 2012 8:16 AM by 952738 RSS

Element Names Query Issue

952738 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you - so obviously when you know how.

Legend

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