CODEWITHSUNDEEP
xmloracle-databasexsd
OraDev001
OraDev001

Reputation: 9

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

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>

Upvotes: 0

Views: 326

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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')

Upvotes: 1

Related Questions