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