Re: XML help
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 6 Feb 2009 07:19:35 -0800 (PST)
Message-ID: <d24d928d-7be3-44e3-b9d9-34a399e8fb94_at_p23g2000prp.googlegroups.com>
On Feb 6, 9:53 am, "NavEEd" <naveed..._at_hotmail.com> wrote:
> I'm in a bit of a pickle with an XML problem I'm having and I can't seem to
> find any sensible answers anywhere.
>
> Below is a sample of the XML that I have to work with. (I filtered out some
> of the fields, the Items actually have about 30 elements.
>
> I need to split this into 3 tables relationally. ORDER table, PACKAGE
> table, ADDRESS table and PACKAGE_ITEMS table. I'm having a hard time
> figuring out how to load this in sensibly. I have no real experience with
> XML, or Java, however I'm painted into a corner here having to load this in.
> I thought about just looping through the XML file and pulling out the keys,
> but I've a feeling that there's something better out there. If you could
> provide any leads or advice I'd be HUGELY appreciative and perhaps even name
> my first child after you.
>
> Thanks in advance.
>
> <?xml version="1.0" standalone='yes'?>
> <Order>
> <orderReference/>
> <orderNumber>2222</orderNumber>
> <orderUserNumber>333</orderUserNumber>
> <comments/>
> <packages>
> <package>
> <packageIndex/>
> <packageWarehouse/>
> <packageWeight/>
> <packageShippingMethod>standard</packageShippingMethod>
> <packageCarrierMethod/>
> <address>
> <firstName>John </firstName>
> <middleName/>
> <lastName>Doe</lastName>
> </address>
> <items>
> <item>
> <itemSku>123456</itemSku>
> <itemQuantity>1</itemQuantity>
> </item>
> <item>
> <itemSku>123456</itemSku>
> <itemQuantity>1</itemQuantity>
> </item>
> </items>
> <packagePaymentStatus/>
> <packagePaidAmount>0</packagePaidAmount>
> </package>
> </packages>
> <extendedFields/>
> </vendaOrder>
Date: Fri, 6 Feb 2009 07:19:35 -0800 (PST)
Message-ID: <d24d928d-7be3-44e3-b9d9-34a399e8fb94_at_p23g2000prp.googlegroups.com>
On Feb 6, 9:53 am, "NavEEd" <naveed..._at_hotmail.com> wrote:
> I'm in a bit of a pickle with an XML problem I'm having and I can't seem to
> find any sensible answers anywhere.
>
> Below is a sample of the XML that I have to work with. (I filtered out some
> of the fields, the Items actually have about 30 elements.
>
> I need to split this into 3 tables relationally. ORDER table, PACKAGE
> table, ADDRESS table and PACKAGE_ITEMS table. I'm having a hard time
> figuring out how to load this in sensibly. I have no real experience with
> XML, or Java, however I'm painted into a corner here having to load this in.
> I thought about just looping through the XML file and pulling out the keys,
> but I've a feeling that there's something better out there. If you could
> provide any leads or advice I'd be HUGELY appreciative and perhaps even name
> my first child after you.
>
> Thanks in advance.
>
> <?xml version="1.0" standalone='yes'?>
> <Order>
> <orderReference/>
> <orderNumber>2222</orderNumber>
> <orderUserNumber>333</orderUserNumber>
> <comments/>
> <packages>
> <package>
> <packageIndex/>
> <packageWarehouse/>
> <packageWeight/>
> <packageShippingMethod>standard</packageShippingMethod>
> <packageCarrierMethod/>
> <address>
> <firstName>John </firstName>
> <middleName/>
> <lastName>Doe</lastName>
> </address>
> <items>
> <item>
> <itemSku>123456</itemSku>
> <itemQuantity>1</itemQuantity>
> </item>
> <item>
> <itemSku>123456</itemSku>
> <itemQuantity>1</itemQuantity>
> </item>
> </items>
> <packagePaymentStatus/>
> <packagePaidAmount>0</packagePaidAmount>
> </package>
> </packages>
> <extendedFields/>
> </vendaOrder>
What version of Oracle? Oracle provides, depending on release, several XML related handing features.
Try looking into dbms_xmlparser, dbms_xmlstore, etc ...
For version 9.2 see Supplied PL/SQL Packages and Type manual on 10g see PL/SQL Packages and Type manual. Manual name varies slightly by release.
HTH -- Mark D Powell -- Received on Fri Feb 06 2009 - 09:19:35 CST