On Thursday, July 6, 2017 at 8:20:33 AM UTC-4, veey...@gmail.com wrote:

Hi,

I tried to parse an XML data from an XML schema and getting error while trying to select the data using SQL. Following are the details related to the XML structure and directory. Would appreciate if anybody could help on the syntax

1. XML to be parsed

<?xml version="1.0" encoding="utf-8"?>
<CheckItemPriceRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<UniqueID>MedicalPortal11263ca2e-6b25-4b5b-bc7a-6f2c213269cc</UniqueID>
<RequestDateTime>3/20/2017 5:27:39 PM</RequestDateTime>
<ItemList>
<Item>
<Date>3/20/17</Date> <!-- Date format mm/dd/yy -->
<ShipTo>1111</ShipTo>
<ItemNumber>1111</ItemNumber>
<Quantity>1</Quantity>
</Item>
</ItemList>
</CheckItemPriceRequest>

2. Fields to be retrieved

"UniqueId",
"Date",
"ShipTo",
"ItemNumber",
"Quantity"

3. xml is residing in the following directory

'/home/level8/POROS/CheckItemPriceRequest.xml'

4. My SQL

select "UniqueId","Date","ShipTo","ItemNumber","Quantity"
from
xmltable(
'$d/CheckItemPriceRequest/ItemList/Item'
passing xmlparse(DOCUMENT
GET_XML_File(
'/home/level8/POROS/CheckItemPriceRequest.xml'
)) as "d"
COLUMNS
"UniqueId" char(50) default ' ' path 'CheckItemPriceRequest/UniqueId',
"Date" char(8) default ' ' path 'CheckItemPriceRequest/ItemList/Item/Date',
"ShipTo" char(8) default ' ' path 'CheckItemPriceRequest/ItemList/Item/ShipTo',
"ItemNumber" char(25) default ' ' path 'CheckItemPriceRequest/ItemList/Item/ItemNumber',
"Quantity" char(5) default ' ' path 'CheckItemPriceRequest/ItemList/Item/Quantity'
) info_req

5. Run time Error

User-defined function error on member QSQPTABL.
Cancel reply received for message CPF503E.

Thanks in advance
Vfb

Did you figure out the problem?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)