redsoxlost
redsoxlost

Reputation: 1235

Parsing XML with XMLTable n Oracle

I have one xml which holds customer and order details. I am parsing it using XMLTable. I think I am giving the correct XPath but I am getting no output.

Here is what I have tried. Hoping for assistance.

**DECLARE
l_raw_xml CLOB:= '<?xml version="1.0" encoding="utf-8"?>
<Root
    xmlns="http://www.adventure-works.com">
    <Customers>
        <Customer CustomerID="GREAL">
            <CompanyName>Great Lakes Food Market</CompanyName>
            <ContactName>Howard Snyder</ContactName>
            <ContactTitle>Marketing Manager</ContactTitle>
            <Phone>(503) 555-7555</Phone>
            <FullAddress>
                <Address>2732 Baker Blvd.</Address>
                <City>Eugene</City>
                <Region>OR</Region>
                <PostalCode>97403</PostalCode>
                <Country>USA</Country>
            </FullAddress>
        </Customer>
    </Customers>
    <Orders>
        <Order>
            <CustomerID>LETSS</CustomerID>
            <EmployeeID>6</EmployeeID>
            <OrderDate>1997-11-10T00:00:00</OrderDate>
            <RequiredDate>1997-12-08T00:00:00</RequiredDate>
            <ShipInfo ShippedDate="1997-11-21T00:00:00">
                <ShipVia>2</ShipVia>
                <Freight>45.97</Freight>
                <ShipName>Let Stop N Shop</ShipName>
                <ShipAddress>87 Polk St. Suite 5</ShipAddress>
                <ShipCity>San Francisco</ShipCity>
                <ShipRegion>CA</ShipRegion>
                <ShipPostalCode>94117</ShipPostalCode>
                <ShipCountry>USA</ShipCountry>
            </ShipInfo>
        </Order>
    </Orders>
</Root>';
l_xml_type XMLTYPE:=XMLTYPE.createXML(l_raw_xml);
cursor c_make_xml_object 
is
    SELECT x.*,
           y.*
      FROM XMLTABLE('/Root/Customers/Customer'
                    PASSING l_xml_type
                    COLUMNS
                        CustomerID  VARCHAR2(255) PATH '@CustomerID',
                        CompanyName VARCHAR2(255) PATH 'CompanyName',
                        FullAddress  XMLTYPE PATH 'FullAddress') x,
           XMLTABLE('/FullAddress'
                    PASSING x.FullAddress
                        COLUMNS
                            Address VARCHAR2(255) PATH 'Address',
                            City    VARCHAR2(255) PATH 'City') y ;

BEGIN
    FOR rec IN c_make_xml_object 
    loop
        DBMS_OUTPUT.PUT_LINE(rec.CustomerID);
        DBMS_OUTPUT.PUT_LINE(rec.CompanyName);
    END LOOP;
END;
/**

Do I have to account for the namespace? I had added namespace code but it did not work/

Upvotes: 0

Views: 378

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65218

Yes, a proper namespace definition is needed within the SQL query such as below :

SELECT x.*, y.*
  FROM XML_TAB t,
       XMLTABLE(XMLnamespaces('http://www.adventure-works.com' as "ns"),
                'ns:Root/ns:Customers/ns:Customer'
                PASSING t.xml_data
                COLUMNS
                    CustomerID  VARCHAR2(255) PATH '@CustomerID',
                    CompanyName VARCHAR2(255) PATH 'ns:CompanyName',
                    FullAddress XMLTYPE       PATH 'ns:FullAddress'
                    ) x,
       XMLTABLE(XMLnamespaces('http://www.adventure-works.com' as "ns"),
                'ns:FullAddress'
                PASSING x.FullAddress
                COLUMNS
                    Address VARCHAR2(255) PATH 'ns:Address',
                    City    VARCHAR2(255) PATH 'ns:City') y ;   

Demo with SQL

Demo with PL/SQL

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

Change to this to include a default namespace:

  FROM XMLTABLE(xmlnamespaces(default 'http://www.adventure-works.com'), 
          '/Root/Customers/Customer'  ....
    ....                   
        XMLTABLE(xmlnamespaces(default 'http://www.adventure-works.com'), '/FullAddress'
                   .....

Upvotes: 1

Related Questions