0

I need to extract to doc_num and x for kod = 'N' from XML like this:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<main doc_num=99>
<item name="A">
    <Dok Kod="N" X="1" Id="5"/>
    <Dok Kod="N" X="4" Id="5"/>
</item>
<item name="B">
    <Dok Kod="N" X="1" Id="2"/>
    <Dok Kod="N" X="4" Id="6"/>
    <Dok Kod="N" X="5" Id="8"/>
    <Dok Kod="Z" X="4553" Id="8"/>
</item>
</main>

Output should look like this, I only need distinct:

doc_num     X
99          1
99          4
99          5 

1 Answer 1

1

You can use XMLTable with an Xpath that targets the Kod=N nodes, then walks back up the tree to get the doc_num; this version assumes your original value is a string:

select distinct xml.doc_num, xml.x
from your_table t
cross apply xmltable (
  '/main/item/Dok[@Kod="N"]'
  passing xmltype(t.your_xml)
  columns doc_num number path './../../@doc_num',
    x number path '@X'
) xml
DOC_NUM X
------- -
     99 1
     99 4
     99 5

db<>fiddle

If your original value is already XMLType then walking back up the tree doesn't work as expected - not sure if that's a bug but seems to happen in 11gR2 and 18c at least. You can work around that by converting to a string and back:

select distinct xml.doc_num, xml.x
from your_table t
cross apply xmltable (
  '/main/item/Dok[@Kod="N"]'
  passing xmltype(t.your_xml.getclobval())
  columns doc_num number path './../../@doc_num',
    x number path '@X'
) xml
DOC_NUM X
------- -
     99 1
     99 4
     99 5

db<>fiddle


I've just noticed you tagged this for Oracle 11g... so cross apply isn't available, and walking back up the tree doesn't work either; neither does filtering on the child attribute name. So you can do this instead, with two levels of XMLTable:

select distinct xml1.doc_num, xml2.x
from your_table t
cross join xmltable (
  '/main'
  passing xmltype(t.your_xml)
  columns doc_num number path '@doc_num',
    doks xmltype path 'item/Dok'
) xml1
cross join xmltable (
  '/Dok'
  passing xml1.doks
  columns kod varchar2(1) path '@Kod',
    x number path '@X'
) xml2
where xml2.kod = 'N'
DOC_NUM X
------- -
     99 1
     99 4
     99 5

which works in 11g with a string or with an XMLType source.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.