Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Umashankar .

Asked: August 29, 2020 - 6:32 pm UTC

Last updated: September 04, 2020 - 12:52 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi
I am not an xml expert and tried to extract the values from xml using xmltable, but i get only partial data.
where i am doing mistake can you guide me ?
Thanks in advance.



XML File:

<?xml version="1.0" encoding="WINDOWS-1252"?>
<ns0:NotificationEnvelope xmlns:ns0="urn:xxxu:xxxx:data:notification:1:standard">
<ns0:DocumentHeader>
</ns0:DocumentHeader>
<ns0:Content>
<ns0:Np>
<ns0:cat>A</ns0:cat>
<ns0:allocation>
<ns0:from>10-AUG-2020</ns0:from>
<ns0:to>11-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>11-AUG-2020</ns0:from>
<ns0:to>12-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>12-AUG-2020</ns0:from>
<ns0:to>13-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
</ns0:Np>
<ns0:Np>
<ns0:cat>B</ns0:cat>
<ns0:allocation>
<ns0:from>10-AUG-2020</ns0:from>
<ns0:to>11-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>11-AUG-2020</ns0:from>
<ns0:to>12-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>12-AUG-2020</ns0:from>
<ns0:to>13-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
</ns0:Np>
<ns0:Np>
<ns0:cat>C</ns0:cat>
<ns0:allocation>
<ns0:from>10-AUG-2020</ns0:from>
<ns0:to>11-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>11-AUG-2020</ns0:from>
<ns0:to>12-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
<ns0:allocation>
<ns0:from>12-AUG-2020</ns0:from>
<ns0:to>13-aug-2020</ns0:to>
<ns0:qty>5</ns0:qty>
</ns0:allocation>
</ns0:Np>
</ns0:Content>
</ns0:NotificationEnvelope>

Expected output:

Cat From to qty
-------- ----- ------- --------
A 10-aug-2020 11-aug-2020 5
B 11-aug-2020 12-aug-2020 5
C 12-aug-2020 13-aug-2020 5


Query:

SELECT xt.*

FROM test x,
XMLTABLE(XMLNAMESPACES('urn:xxxu:xxxx:data:notification:1:standard' as
"ns0"),
'/ns0:NotificationEnvelope/ns0:Content/ns0:NP'
PASSING x.user_data
COLUMNS
UserCategory VARCHAR2(50) PATH 'ns0:cat',
FROM VARCHAR2(20) PATH '/ns0:Allocation/ns0:from',
TO VARCHAR2(20) PATH '/ns0:Allocation/ns0:to',
QUANTITY NUMBER PATH '/ns0:Allocation/ns0:qty',
) xt


query output:

Cat From to qty
-------- ----- ------- --------
A NULL NULL Null
B NULL NULL Null
C NULL NULL Null



and Connor said...

Your XML doesn't support the result you want, because there are nested from/to pairs for each category.

Maybe something like this 


SQL> with test as
  2  ( select xmltype(
  3  '<?xml version="1.0" encoding="WINDOWS-1252"?>
  4  <ns0:NotificationEnvelope xmlns:ns0="urn:xxxu:xxxx:data:notification:1:standard">
  5  <ns0:DocumentHeader>
  6  </ns0:DocumentHeader>
  7  <ns0:Content>
  8  <ns0:Np>
  9  <ns0:cat>A</ns0:cat>
 10  <ns0:allocation>
 11  <ns0:from>10-AUG-2020</ns0:from>
 12  <ns0:to>11-aug-2020</ns0:to>
 13  <ns0:qty>5</ns0:qty>
 14  </ns0:allocation>
 15  <ns0:allocation>
 16  <ns0:from>11-AUG-2020</ns0:from>
 17  <ns0:to>12-aug-2020</ns0:to>
 18  <ns0:qty>5</ns0:qty>
 19  </ns0:allocation>
 20  <ns0:allocation>
 21  <ns0:from>12-AUG-2020</ns0:from>
 22  <ns0:to>13-aug-2020</ns0:to>
 23  <ns0:qty>5</ns0:qty>
 24  </ns0:allocation>
 25  </ns0:Np>
 26  <ns0:Np>
 27  <ns0:cat>B</ns0:cat>
 28  <ns0:allocation>
 29  <ns0:from>10-AUG-2020</ns0:from>
 30  <ns0:to>11-aug-2020</ns0:to>
 31  <ns0:qty>5</ns0:qty>
 32  </ns0:allocation>
 33  <ns0:allocation>
 34  <ns0:from>11-AUG-2020</ns0:from>
 35  <ns0:to>12-aug-2020</ns0:to>
 36  <ns0:qty>5</ns0:qty>
 37  </ns0:allocation>
 38  <ns0:allocation>
 39  <ns0:from>12-AUG-2020</ns0:from>
 40  <ns0:to>13-aug-2020</ns0:to>
 41  <ns0:qty>5</ns0:qty>
 42  </ns0:allocation>
 43  </ns0:Np>
 44  <ns0:Np>
 45  <ns0:cat>C</ns0:cat>
 46  <ns0:allocation>
 47  <ns0:from>10-AUG-2020</ns0:from>
 48  <ns0:to>11-aug-2020</ns0:to>
 49  <ns0:qty>5</ns0:qty>
 50  </ns0:allocation>
 51  <ns0:allocation>
 52  <ns0:from>11-AUG-2020</ns0:from>
 53  <ns0:to>12-aug-2020</ns0:to>
 54  <ns0:qty>5</ns0:qty>
 55  </ns0:allocation>
 56  <ns0:allocation>
 57  <ns0:from>12-AUG-2020</ns0:from>
 58  <ns0:to>13-aug-2020</ns0:to>
 59  <ns0:qty>5</ns0:qty>
 60  </ns0:allocation>
 61  </ns0:Np>
 62  </ns0:Content>
 63  </ns0:NotificationEnvelope>') user_data from dual
 64  )
 65  SELECT xt.UserCategory, xt2.xfrom, xt2.xto, xt2.xQUANTITY
 66  FROM test x,
 67    XMLTABLE(XMLNAMESPACES('urn:xxxu:xxxx:data:notification:1:standard' as "ns0"),
 68      '/ns0:NotificationEnvelope/ns0:Content/ns0:Np'
 69      PASSING x.user_data
 70    COLUMNS
 71      UserCategory VARCHAR2(10) PATH '/ns0:Np/ns0:cat',
 72      alloc xmltype PATH '/ns0:Np/ns0:allocation'
 73    ) xt,
 74    xmltable(
 75      XMLNAMESPACES('urn:xxxu:xxxx:data:notification:1:standard' as "ns0"),
 76      '/ns0:allocation'
 77      passing xt.alloc
 78      columns
 79          xFROM VARCHAR2(20) PATH '/ns0:allocation/ns0:from',
 80          xTO VARCHAR2(20) PATH '/ns0:allocation/ns0:to',
 81          xQUANTITY NUMBER PATH '/ns0:allocation/ns0:qty'
 82      ) xt2
 83    ;

USERCATEGO XFROM                XTO                   XQUANTITY
---------- -------------------- -------------------- ----------
A          10-AUG-2020          11-aug-2020                   5
A          11-AUG-2020          12-aug-2020                   5
A          12-AUG-2020          13-aug-2020                   5
B          10-AUG-2020          11-aug-2020                   5
B          11-AUG-2020          12-aug-2020                   5
B          12-AUG-2020          13-aug-2020                   5
C          10-AUG-2020          11-aug-2020                   5
C          11-AUG-2020          12-aug-2020                   5
C          12-AUG-2020          13-aug-2020                   5

9 rows selected.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 02, 2020 - 12:49 pm UTC

Thanks Connor worked perfectly
Connor McDonald
September 03, 2020 - 2:53 am UTC

glad we could help

how about performance using xmltable V/s Extractvalue

Umashankar, September 03, 2020 - 9:12 am UTC

how about performance which one is best? XMLtable or extract value ?

Assume in the XML file returns 8000 records for different category,time period and quantity




Connor McDonald
September 04, 2020 - 12:52 am UTC

that would be the part where you benchmark it :-)

Because there is no "answer" to:

"which one is best? XMLtable or extract value ?"

If one was *always* better, we would remove the other one entirely from the software
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library