XMLTABLE question
David Day <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <MMXP12301MB14691047596D8E7E93F74592AF030@MMXP12301MB1469.GBRP123.PROD.OUTLOOK.COM> |
Hi, I was hoping to get some advice and potentially a solution to my problem. I have put a test case together as per attachment with the error I am getting when using the XMLTABLE function. I am trying to migrate my code from Oracle to Postgresql so just wondering how best to do this using this XMLTABLE option for this particular scenario. Kind regards David Day Oracle Developer CDL [http://www.cdl.co.uk/images/cdllogo.png]<http://www.cdl.co.uk/> [http://www.cdl.co.uk/images/footer/twitter.png]<http://twitter.com/CDL_Software>[http://www.cdl.co.uk/images/facebook.png]<http://www.facebook.com/CDL-Software>[http://www.cdl.co.uk/images/footer/linkedin.png]<http://www.linkedin.com/company/cdl-cheshire-datasystems-ltd-> Please consider the environment - Do you really need to print this email? This email is intended only for the person(s) named above and may contain private and confidential information. If it has come to you in error, please destroy and permanently delete any copy in your possession, and contact us on +44 (0)161 480 4420. The information in this email is copyright (c) CDL Group Holdings Limited. We cannot accept liability for any loss or damage sustained as a result of software viruses. It is your responsibility to carry out such virus checking as is necessary before opening any attachment. Cheshire Datasystems Limited uses software which automatically screens incoming emails for inappropriate content and attachments. If the software identifies such content or attachment, the email will be forwarded to our Technology department for checking. You should be aware that any email that you send to Cheshire Datasystems Limited is subject to this procedure. ________________________________ Cheshire Datasystems Limited, Strata House, Kings Reach Road, Stockport, SK4 2HD Registered in England and Wales with company number 3991057 VAT registration: 727 1188 33
XMLTABLE_test_case_example.txt
(text/plain, 2.7 KB)
I was hoping to get some advice and potentially a solution to my problem.
This example is just showing the concept of what we're trying to do as the XML structure we hold can be over 2,000 lines.
In Oracle we're migrating our code into postgres so this is how we're currently doing it in Oracle and what we're trying to migrate into postgres. One of the problems we're hitting is using MULTIPLE XMLTABLE functions to link nodes with a single XML tag elements to one's with multiple tag elements as show in this example. The problem with the XMLTABLE in postgres we don't seem to be able to pass structures from one XMLTABLE to another to structure the output as we require.
Here is a test case example:-
CREATE TABLE XML_TABLE (
id NUMERIC(19,0) NOT NULL,
resultxml XML
);
INSERT into XML_TABLE(ID,RESULTXML)
VALUES
(1,'<storedresults>
<result>
<product>AA</product>
<name>XX</name>
<items>
<item>
<text>test item 1</text>
<value>29.9</value>
</item>
<item>
<text>test item 2</text>
<value>30.9</value>
</item>
</items>
</result>
<result>
<product>BB</product>
<name>ZZ</name>
<items>
<item>
<text>test item 1</text>
<value>30.9</value>
</item>
<item>
<text>test item 2</text>
<value>40.9</value>
</item>
</items>
</result>
<result>
<product>CC</product>
<name>YY</name>
<items>
<item>
<text>test item 1</text>
<value>50.9</value>
</item>
<item>
<text>test item 2</text>
<value>60.9</value>
</item>
</items>
</result>
</storedresults>');
SELECT xt.id ,
xt1.RESULT_POS,
xt1.product,
xt1.name,
xt2.item_pos,
xt2.item_text,
xt2.item_value
FROM TEST_LOAD.XML_TABLE xt,
XMLTABLE('//storedresults/result'
PASSING xt.resultxml
COLUMNS RESULT_POS FOR ORDINALITY,
PRODUCT CHARACTER VARYING(20) path 'product',
NAME CHARACTER VARYING(20) path 'name',
ITEMS_XML XML PATH '//items/item') xt1,
XMLTABLE('item'
PASSING xt1.ITEMS_XML
COLUMNS
ITEM_POS FOR ORDINALITY,
ITEM_TEXT CHARACTER VARYING(300) PATH 'text',
ITEM_VALUE CHARACTER VARYING(300) PATH 'value') xt2
WHERE xt.id = 1;
I get the following error.
ERROR: could not parse XML document
DETAIL: line 4: Extra content at the end of the document
</item><item>
In Oracle it works fine doing this so just wondering if there is another way of doing this in Postgres that is not going to be massive on performance consumption as our the LIVE solution is dealing with high volumes of data which extract data from a large XML structure (this is just an simplified example).
Thanks
David