Forum Stats

  • 3,817,231 Users
  • 2,259,293 Discussions
  • 7,893,705 Comments

Discussions

Categories

XML and multiple namespaces

user13117585
user13117585 Member Posts: 659 Bronze Badge
edited Sep 15, 2016 2:53AM in SQL & PL/SQL

Hello,

I have a quick question about XMLType and how to extract values from it... I have one with many namespaces like this one:

WITH T AS (   SELECT '   <project xmlns="http://www.test.com/xid/v2">     <Node>NodeValue</Node>     <Content>       <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">         <Details>           <Id type="Local">123456</Id>           <Id type="Remote">654321</Id>         </Details>       </Document:Document>     </Content>    </project>' x FROM dual  )  SELECT ExtractValue(XMLType(x), '/project/Node', 'xmlns="http://www.test.com/xid/v2"'),        ExtractValue(XMLType(x), '/project/Content/Document/Details/Id[@type="Local"]', 'xmlns="http://www.test.com/xid/v2"')  FROM t;

And when I try to extract Local Id of a document, it always returns null. Any idea why? I'm using Oracle 11.2;

Thank you for your help.

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Sep 14, 2016 3:28AM Answer ✓

    Hi,

    persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/'

    Almost there, you're missing the last step of the XPath expression : 'ns1:Person'

    '/ns0:project'passing xmlparse(x.persons)columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]'

    Three mistakes here :

    1) You're already passing an XMLType from the first XMLTABLE so you must not use XMLParse here (and there's a syntax error anyway)

    2) You're passing a collection of 'Person' so why do you try to extract 'ns0:project' ?

    3) The PATH expression is wrong, because of (2)

    Here's the working query.

    Don't hesitate to ask further questions if necessary.

    SQL> WITH T AS (  2    SELECT '<project xmlns="http://www.test.com/xid/v2">  3      <Node>NodeValue</Node>  4      <Content>  5        <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">  6          <Details>  7            <Id type="Local">123456</Id>  8            <Id type="Remote">654321</Id>  9      <Person> 10        <Id type="Name">Foo</Id> 11      </Person> 12      <Person> 13        <Id type="Name">Test</Id> 14      </Person> 15      <Person> 16        <Id type="Name">Sub</Id> 17      </Person> 18          </Details> 19        </Document:Document> 20      </Content> 21    </project>' x 22  FROM dual 23  ) 24  SELECT x.node, x.local_id, y.pName 25  FROM t 26      , XMLTABLE( 27          XMLNamespaces( 28            'http://www.test.com/xid/v2' as "ns0" 29          , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 30          ) 31        , '/ns0:project' 32          passing xmlparse(document t.x) 33          columns node      varchar2(15) path 'ns0:Node' 34                , local_id  number       path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 35                , persons   xmltype      path 'ns0:Content/ns1:Document/ns1:Details/ns1:Person' 36        ) x 37      , XMLTable( 38          XMLNamespaces( 39            default 'http://other.domain.be/prj/types/prj/file/1' 40          ) 41        , '/Person' 42          passing x.persons 43          columns pName VARCHAR2(10) PATH 'Id[@type="Name"]' 44        ) y 45    ;NODE              LOCAL_ID PNAME--------------- ---------- ----------NodeValue           123456 FooNodeValue           123456 TestNodeValue           123456 Sub
    user13117585
    · Share on TwitterShare on Facebook

Answers

  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Aug 31, 2016 8:40AM

    First of all, EXTRACTVALUE and other related proprietary functions are all deprecated.

    Use standard XQuery-based functions XMLQuery - when you need a single value - or XMLTable when you have to extract more than one value at a time or generate a row source.

    XMLTable is more appropriate in this case as it allows you to :

    - parse the XML content once

    - use the handy XMLNamespaces clause to declare namespace mappings.

    Please read this as well, it provides a general methodology to correctly declare and use namespaces :

    https://odieweblog.wordpress.com/2016/06/07/xml-namespaces-101/

    WITH T AS (  
       SELECT '<project xmlns="http://www.test.com/xid/v2">  
         <Node>NodeValue</Node>  
         <Content>  
           <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">  
             <Details>  
               <Id type="Local">123456</Id>  
               <Id type="Remote">654321</Id>  
             </Details>  
           </Document:Document>  
         </Content>   
       </project>  
    ' x   
    FROM dual    
    )    
    SELECT x.*  
    FROM t
       , XMLTABLE(
           XMLNamespaces(
             'http://www.test.com/xid/v2' as "ns0"
           , 'http://other.domain.be/prj/types/prj/file/1' as "ns1"
           )
         , '/ns0:project'
           passing xmlparse(document t.x)
           columns node      varchar2(15) path 'ns0:Node'
                 , local_id  number       path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]'
         ) x
    ;
    user13117585
    · Share on TwitterShare on Facebook
  • user13117585
    user13117585 Member Posts: 659 Bronze Badge
    edited Aug 31, 2016 8:51AM

    Thank you Odie. I will also have a look at your blog.

    I will come back to you.

    · Share on TwitterShare on Facebook
  • user13117585
    user13117585 Member Posts: 659 Bronze Badge
    edited Sep 14, 2016 3:12AM

    Hello Odie,

    Thank you for your answer. It works quite great. However, I have an additional question (if you don't mind). Imagine I have the following structure (multi nodes Person) and I want to extract the name and have multi records. Do you think it's doable? I tried with many XMLTable but can't find a way Do you have any idea?

    WITH T AS (     SELECT '<project xmlns="http://www.test.com/xid/v2">       <Node>NodeValue</Node>       <Content>         <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">           <Details>             <Id type="Local">123456</Id>             <Id type="Remote">654321</Id>    <Person>      <Id type="Name">Foo</Id>       </Person>    <Person>      <Id type="Name">Test</Id>       </Person>    <Person>      <Id type="Name">Sub</Id>       </Person>        </Details>         </Document:Document>       </Content>     </project>   ' x   FROM dual     )     SELECT x.* , y.* FROM t   , XMLTABLE(       XMLNamespaces(         'http://www.test.com/xid/v2' as "ns0"       , 'http://other.domain.be/prj/types/prj/file/1' as "ns1"       )     , '/ns0:project'       passing xmlparse(document t.x)       columns node      varchar2(15) path 'ns0:Node'             , local_id  number      path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]'            , persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/'    ) x,       XMLTAble(      XMLNamespaces(         'http://www.test.com/xid/v2' as "ns0"       , 'http://other.domain.be/prj/types/prj/file/1' as "ns1"       ),      '/ns0:project'      passing xmlparse(x.persons)      columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]'      ) y;
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Sep 14, 2016 3:28AM Answer ✓

    Hi,

    persons XMLTYpe PATH 'ns0:Content/ns1:Document/ns1:Details/'

    Almost there, you're missing the last step of the XPath expression : 'ns1:Person'

    '/ns0:project'passing xmlparse(x.persons)columns pName VARCHAR2(10) PATH 'ns0:Content/ns1:Document/ns1:Details/ns1:Person/ns1:Id[@type="Name"]'

    Three mistakes here :

    1) You're already passing an XMLType from the first XMLTABLE so you must not use XMLParse here (and there's a syntax error anyway)

    2) You're passing a collection of 'Person' so why do you try to extract 'ns0:project' ?

    3) The PATH expression is wrong, because of (2)

    Here's the working query.

    Don't hesitate to ask further questions if necessary.

    SQL> WITH T AS (  2    SELECT '<project xmlns="http://www.test.com/xid/v2">  3      <Node>NodeValue</Node>  4      <Content>  5        <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">  6          <Details>  7            <Id type="Local">123456</Id>  8            <Id type="Remote">654321</Id>  9      <Person> 10        <Id type="Name">Foo</Id> 11      </Person> 12      <Person> 13        <Id type="Name">Test</Id> 14      </Person> 15      <Person> 16        <Id type="Name">Sub</Id> 17      </Person> 18          </Details> 19        </Document:Document> 20      </Content> 21    </project>' x 22  FROM dual 23  ) 24  SELECT x.node, x.local_id, y.pName 25  FROM t 26      , XMLTABLE( 27          XMLNamespaces( 28            'http://www.test.com/xid/v2' as "ns0" 29          , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 30          ) 31        , '/ns0:project' 32          passing xmlparse(document t.x) 33          columns node      varchar2(15) path 'ns0:Node' 34                , local_id  number       path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 35                , persons   xmltype      path 'ns0:Content/ns1:Document/ns1:Details/ns1:Person' 36        ) x 37      , XMLTable( 38          XMLNamespaces( 39            default 'http://other.domain.be/prj/types/prj/file/1' 40          ) 41        , '/Person' 42          passing x.persons 43          columns pName VARCHAR2(10) PATH 'Id[@type="Name"]' 44        ) y 45    ;NODE              LOCAL_ID PNAME--------------- ---------- ----------NodeValue           123456 FooNodeValue           123456 TestNodeValue           123456 Sub
    user13117585
    · Share on TwitterShare on Facebook
  • user13117585
    user13117585 Member Posts: 659 Bronze Badge
    edited Sep 14, 2016 3:38AM

    Thank you Odie. You are the best!!!

    Thank you!!!

    · Share on TwitterShare on Facebook
  • user13117585
    user13117585 Member Posts: 659 Bronze Badge
    edited Sep 15, 2016 2:21AM

    Odie, if I may... one last question....

    Imagine I have the following (I added two nodes Entity in the Details). Is it possible to extract the values of both at the same time? Something like this:

    , entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username',, entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username'

    This doesn't return anything but I was curious to know if it was possible.

    Thank you,

    WITH T AS (     SELECT '<project xmlns="http://www.test.com/xid/v2">       <Node>NodeValue</Node>       <Content>         <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">           <Details>             <Id type="Local">123456</Id>             <Id type="Remote">654321</Id>          <Entity>            <person>              <username>foo</username>            </person>          </Entity>                   <Entity>            <type>physical</type>            <person>              <username>bar</username>            </person>          </Entity>                 </Details>         </Document:Document>       </Content>     </project>   ' x   FROM dual     )     SELECT x.*   FROM t   , XMLTABLE(       XMLNamespaces(         'http://www.test.com/xid/v2' as "ns0"       , 'http://other.domain.be/prj/types/prj/file/1' as "ns1"       )     , '/ns0:project'       passing xmlparse(document t.x)       columns node      varchar2(15) path 'ns0:Node'             , local_id  number      path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]'             , entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username',  , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username'    ) x ;
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Sep 15, 2016 2:53AM 
    user13117585 wrote:Imagine I have the following (I added two nodes Entity in the Details). Is it possible to extract the values of both at the same time? Something like this:, entityName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person/username',  , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/person[type="Physical"]/username'

    Again, multiple issues :

    1) person, username and type elements belong to the 'http://other.domain.be/prj/types/prj/file/1' namespace so you're missing the 'ns1' prefix.

    2) type is a child of Entity, not person therefore the predicate 'person[type="Physical"]' doesn't target anything.

    3) the type is 'physical', not 'Physical'.

    However, If you correct (1) you'll get :

    ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

    because 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity/ns1:person/ns1:username' targets multiple nodes.

    Do you want this?

    SQL> WITH T AS (  2    SELECT '<project xmlns="http://www.test.com/xid/v2">  3      <Node>NodeValue</Node>  4      <Content>  5        <Document:Document xmlns:Document="http://other.domain.be/prj/types/prj/file/1" xmlns="http://other.domain.be/prj/types/prj/file/1">  6          <Details>  7            <Id type="Local">123456</Id>  8            <Id type="Remote">654321</Id>  9            <Entity> 10              <person> 11                <username>foo</username> 12              </person> 13            </Entity> 14            <Entity> 15              <type>physical</type> 16              <person> 17                <username>bar</username> 18              </person> 19            </Entity> 20          </Details> 21        </Document:Document> 22      </Content> 23    </project>' x 24  FROM dual 25  ) 26  SELECT x.* 27  FROM t 28    , XMLTABLE( 29        XMLNamespaces( 30          'http://www.test.com/xid/v2' as "ns0" 31        , 'http://other.domain.be/prj/types/prj/file/1' as "ns1" 32        ) 33      , '/ns0:project' 34        passing xmlparse(document t.x) 35        columns node               varchar2(15)  path 'ns0:Node' 36              , local_id           number        path 'ns0:Content/ns1:Document/ns1:Details/ns1:Id[@type="Local"]' 37              , entityName         varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity[not(ns1:type)]/ns1:person/ns1:username' 38              , entityPhysicalName varchar2(255) path 'ns0:Content/ns1:Document/ns1:Details/ns1:Entity[ns1:type="physical"]/ns1:person/ns1:username' 39      ) x 40  ;NODE              LOCAL_ID ENTITYNAME   ENTITYPHYSICALNAME--------------- ---------- ------------ -------------------NodeValue           123456 foo          bar
    user13117585
    · Share on TwitterShare on Facebook
This discussion has been closed.