XML and XSD loading into oracle table and extract value from table

Question

I am new to XML and XML Schema. I want to extract the customer id and customer name from the following XML pass these two values to PL/SQL procedure. Could you please help me on this? Thanks in advance.

XML: 

<?xml version="1.0" encoding="UTF-8"?>
-<ns0:NewConnection xmlns:ns0="http://xyz.co.uk/cust">
<Customer>SA02121</Customer>
<Customer name>John</Customer name>
<Load Date>2016-11-09</Load Date>
<Load Time>03:40:12</Load Time>
</ns0:NewConnection>

where does this xml come from? Is it being selected from a table (and in fact you're looping through multiple rows) or is it a single xml document that's being passed into a pl/sql procedure? — Boneist
– Boneist, Commented Nov 21, 2016 at 11:29

Arkadiusz Łukasiewicz · Accepted Answer · 2016-11-21 13:01:32Z

Your xml isn't xml. :)

XML Naming Rules XML elements must follow these naming rules:

Element names are case-sensitive
Element names must start with a letter or underscore
Element names cannot start with the letters xml (or XML, or Xml, etc)
Element names can contain letters, digits, hyphens, underscores, and periods
Element names cannot contain spaces

If your replace sapces with underscore in your tags. You can obtain data using xmltabel.

       select *
         from xmltable(xmlnamespaces('http://xyz.co.uk/cust' as "ns0"), '/ns0:NewConnection'
                       passing xmltype('<?xml version="1.0" encoding="UTF-8"?>
<ns0:NewConnection xmlns:ns0="http://xyz.co.uk/cust">
<Customer>SA02121</Customer>
<Customer_name>John</Customer_name>
<Load_Date>2016-11-09</Load_Date>
<Load_Time>03:40:12</Load_Time>
</ns0:NewConnection>')
                       columns Customer varchar2(200) path 'Customer'
                             , Customer_name varchar2(200) path 'Customer_name'
                             , Load_Date varchar2(200) path 'Load_Date'
                             , Load_Time varchar2(200) path 'Load_Time')

Collectives™ on Stack Overflow

XML and XSD loading into oracle table and extract value from table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related