RE: AW: SQL XML. XMLTABLE 7.1 -- MIDRANGE-L

Thanks Mark!

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of MARK GOLDEN
Sent: Monday, October 06, 2014 8:40 AM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL XML. XMLTABLE 7.1

Hi Gary,

Until now I have been using the RPG-SAX procedure to parse XML documents. This is good but the 7.1 SQL XML seems to have definite advantages.

Start trawling the web for things such as XMLTABLE, GET_XML_FILE (Thanks to Birgetta for this one).
Scan for decomposition or shedding XML.

I'm new to the 7.1 options so as you said all help appreciated.

Regards,
Mark.

________________________________
From: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, 6 October 2014, 14:42
Subject: RE: AW: SQL XML. XMLTABLE 7.1

Interesting - about time Team Mudville learns about XML so any hints on where to start are appreciated.

We're at V7R1

I don't see the XML tool-kit on our V7R1 box so I'll ask ops mgr to install 5733-XT2

We have the XML Files, Ins and Outs and Integrating XML with DB2 redbooks and
XML Toolkit and XML Extender manuals.

Also have XML Toolkit article from ibmsystemsmag . . .

Thanks

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Sunday, October 05, 2014 10:26 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: AW: SQL XML. XMLTABLE 7.1

It is hard to say what happens, without knowing the XML document.
In either way if it is stored in the IFS in the path /erl/customerinfo, can you access the XML document as follows (from IBM I Navigator's run an SQL script / STRSQL does not support it!)

Values(XMLParse(Get_XML_File('/erl/customerinfo/YourxmlDoc.xml'));

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 22:10
An: Midrange Systems Technical Discussion
Betreff: 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.