Re: AW: SQL XML. XMLTABLE 7.1

Hi Birgitta,

This is still new to me as you can see!!

My XML document is stored in the IFS with path /erl/customerinfo.

Mit freundlichen Grüßen / Best regards


Mark Golden.


________________________________
From: Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Sent: Sunday, 5 October 2014, 19:26
Subject: AW: SQL XML. XMLTABLE 7.1


Where is your XML Document located or what's the name of your XML document?
If your XML document is located within the IFS you need to refer to the IFS
file, i.e. retrieve a XML locator with the GET_XML_FILE function:
Something like this:

  Select a.*
  from XMLTABLE('/customerinfo'
        passing xmlparse(Document
Get_xml_file('/YourDir/YourSubDir/YourXMLDoc.xml'))
                Columns  CustId  Integer  Path '@CId',
                        CustName    VarChar(30)  Path 'name',
                        Str  VarChar(40)  Path 'assr/street',
                        Place      VarChar(30) Path 'addr/city')  a;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von MARK
GOLDEN
Gesendet: Sunday, 05.10 2014 15:44
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: SQL XML. XMLTABLE 7.1

Any help would be greatly appreciated.

I am trying to use SQL, XML, XMLTABLE functionality on IBM i 7.1.

Using the below example from the web which is similar to other examples I am
getting the following error.

INSERT INTO erl/address(cid, name, street, city) SELECT x.custid,
x.custname, x.str, x.place FROM XMLTABLE('$i/customerinfo' PASSING CAST(? AS
XML) AS "i"
COLUMNS custid INTEGER PATH '@Cid', custname VARCHAR(30) PATH 'name', str
VARCHAR(40) PATH 'addr/street', place VARCHAR(30) PATH 'addr/city' ) AS x ;
                 


Use of parameter marker not valid.     SQL0418. This seems to refer to the
'?' in the CAST section of the statement.  


 

For more info. I am trying to load a XML file into a Physical file without
XML fields/columns defined. So straight XML to PF file field mapping. 

Yours Sincerely,

Mark Golden
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.