Re: SQL XML. XMLTABLE 7.1 -- MIDRANGE-L

Hi Jon,

I will arrange to have the code posted tomorrow. Unfortunately I do not have access to the IBM i server tonight.

Regards,
Mark

________________________________
From: Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To: Midrange-L Midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, 9 October 2014, 21:11
Subject: Re: SQL XML. XMLTABLE 7.1

For those of us still struggling with the arcane SQL syntax used to perform this feat Mark it would be really nice if you could post the code to the thread or on code.midrange.com and the link posted here.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Oct 9, 2014, at 1:21 PM, MARK GOLDEN <mark_golden@xxxxxxxxxxxxxxx> wrote:

Hi,

Due to your help and pointers I now have an XML file being converted to DB2 with the use to XMLTABLE, XML_GET_FILE, etc.

I do have one more question. Within the data is CDATA strings. I know their purpose but the parser is returning invalid data. Between the ![CDATA[' and ']]' is good but the ![[] is being returned with invalid data. Is this the old problem of [] being translated incorrectly due to CCSIDs or is there
something I can do within the XML process to strip these characters?

Thanks,
Mark.

________________________________
From: Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, 6 October 2014, 19:56
Subject: Re: AW: SQL XML. XMLTABLE 7.1

Excellent!

On 10/6/2014 1:41 PM, Gary Thompson wrote:

Vern,
Thank you.
Both links look spot on for me!

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

Gary

For more on the support in SQL, go to this link -

http://ibmsystemsmag.blogs.com/i_can/xml/

Page down to find the 1st of 2 articles by Nick Lawrence - a number of links to helpful and sometimes almost overwhelming stuff from IBM.

To get going on XML itself, I suggest http://www.w3schools.com and look for the XML tutorial - excellent place for lots of web-related stuff.

HTH
Vern

On 10/6/2014 8:42 AM, Gary Thompson wrote:

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.

--
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.

--
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.
--
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.