How to extract Oracle XML when url part of tag

user_22523 0
Viewed 100

I don't know XML well enough to know what terms to use to ask the question. I'm going to obfuscate the XML snippet I'm trying to decode, hopefully not to the point of making the question unanswerable.

I have some XML, and I want to get the values "First String to Get" and "Second String to Get". How do I write an Oracle query to get them?

<itemTypes>
<itemTypesList>
    <itemType>400SVFD2</itemType>
    <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
    <errorIfNoValue>C1NO</errorIfNoValue>
    <valueType>U</valueType>
    <valueSource>C1BF</valueSource>
    <value/>
    <billFactor>400SVFD2</billFactor>
    <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
    <valueAlgorithm/>
</itemTypesList>

If I write an XML query and put in the entire tag, what happens if some of those fields change? So I think I should be able to indicate just "ora:itemType" and "ora:billFactor", and essentially ignore the rest, right?

This returns null:

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/ora:itemType/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res
from test_table
;
oracle11g oracle xml
2 Answers

First variant (using xmlquery with xpath filter):

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select 
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:itemType[namespace-uri()="http://www.oracle.com/something/somethingelse"]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res1
 ,xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:billFactor[namespace-uri()="http://www.oracle.com/something/somethingelse"]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res2
from test_table
/

Result:

RES1                           RES2
------------------------------ ------------------------------
First String to Get            Second String to Get

NB Your element is in xmlnamespace 'ora', so you need to specify it, but since xmlquery has no XMLNAMESPACES parameter, you have 2 choices to specify them:

  1. add xmlnamespace declaration in the beggining of your xquery:
'declare namespace ora = "http://www.oracle.com/something/somethingelse";...
  1. or filter elements using function namespace-uri():
*:itemType[namespace-uri()="http://www.oracle.com/something/somethingelse"]

*:element means that you need element from any xmlnamespaces. [namespace-uri()="..."] filters elements by their namespaces.

Second variant: usint xmltable(xmlnamespaces(...)...)

with test_table(xmldata) as (
select 
q'[
<root>
    <itemTypes>
    <itemTypesList>
        <itemType>400SVFD2</itemType>
        <ora:itemType xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="itemTypeMaint" searchZone="C1-ITEMTYPQ" tblKeyField="ITEM_TYPE_CD">First String to Get</ora:itemType>
        <errorIfNoValue>C1NO</errorIfNoValue>
        <valueType>U</valueType>
        <valueSource>C1BF</valueSource>
        <value/>
        <billFactor>400SVFD2</billFactor>
        <ora:billFactor xmlns:ora="http://www.oracle.com/something/somethingelse" navOpt="billFactorMaint" searchZone="C1-BFQ" tblKeyField="BF_CD">Second String to Get</ora:billFactor>
        <valueAlgorithm/>
    </itemTypesList>
</itemTypes>
</root>
]'
from dual
)
select 
  xx.*
from test_table
    ,xmltable(
        xmlnamespaces('http://www.oracle.com/something/somethingelse' as "ORA", default ''),
        '/root/itemTypes/itemTypesList'
         passing xmltype(xmldata)
         columns
             res1   varchar2(100) path 'ORA:itemType/text()'
            ,res2   varchar2(100) path 'ORA:billFactor/text()'
    ) xx
;

Results:

RES1                           RES2
------------------------------ ------------------------------
First String to Get            Second String to Get

1 row selected.

And a couple of shorter variants based on wildcard namespaces and filters too:

select
  xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:itemType[namespace-uri()!=""]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res1
  ,xmlcast(
     xmlquery(
        '/root/itemTypes/itemTypesList/*:billFactor[2]/text()'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res2
from test_table

First one returns element with non-empty namespace-uri and second one just returns second billFactor without filtering by namespaces

user_4291000

The URL is a namespace. You can declare that in your XPath. But ora seems to be reserved as it still gets null with that, so you can change it to something else (just in the query, not in the XML document):

        'declare namespace xyz="http://www.oracle.com/something/somethingelse";
         /root/itemTypes/itemTypesList/xyz:itemType'

so in context:

select
  xmlcast(
     xmlquery(
        'declare namespace xyz="http://www.oracle.com/something/somethingelse";
         /root/itemTypes/itemTypesList/xyz:itemType'
         passing xmltype(xmldata)
         returning content
     )
     as varchar2(100)
   ) res
from test_table

db<>fiddle

As you want multiple values you're probably better off with XMLTable:

select x.*
from test_table tt
cross join xmltable(
  xmlnamespaces('http://www.oracle.com/something/somethingelse' as "xyz"),
  '/root/itemTypes/itemTypesList'
  passing xmltype(tt.xmldata)
  columns first_str varchar2(30) path 'xyz:itemType',
    second_str varchar2(30) path 'xyz:billFactor'
) x

db<>fiddle

Read more

user_2663040
Related