× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2014

Re: SQL XML. XMLTABLE 7.1

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.