Re: Reading XML with SQL XMLTABLE

Hi Gio,

You can use SQL function XMLTABLE() to parse XML in a variable. I have a
working function that does that which I use a lot. A portion of that code
looks like this:

xmlparse( document STRING_VARIABLE_CONTAINING_XML ) as MY_XML

The MY_XML result is what I pass to the XMLTABLE() function via the
"PASSING" clause, followed by a COLUMNS clause containing PATH xpath
commands to define which XML elements to extract. The extracted elements
become part of your SQL result set.

I code new XMLTABLE() stuff very infrequently, so I'm slow at it, and by no
means are familiar with all its capabilities. So, I suspect it is capable
of achieving what you want in multiple ways.

I suspect you may need to do something closer to:

columns
faultstring varchar(128) path '@faultstring'

I don't know if you can get it to work without the PATH piece or not.

For the limited times I've used XMLTABLE, I'm happy with how much it can
accomplish with such little code. Hence, the next time I need to parse some
XML on the IBM i, XMLTABLE() is what I'll use.

Mike


date: Wed, 31 Jan 2018 16:57:07 +0100

from: "GioArt" <giovanni.arturi@xxxxxxxxx>
subject: Reading XML with SQL XMLTABLE

Hi all



I would try to read the following Xml with XMLTABLE function, but I don't
understand what I have to compose

the Sql statement



I tried :

Select a.* from XMLTABLE('Body' passing( xmlparse(Document

Get_xml_file('/tmp/txml.xml'))) columns faultstring

varchar(256) ) as a



This the xml content :

<?xml version="1.0" encoding="UTF-8"?>

<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/";>

<S:Body>

<S:Fault xmlns:ns4="http://www.w3.org/2003/05/soap-envelope";>

<faultcode>S:Cli ent</faultcode>

<faultstring>1:Invalid username or password</faultstring>

</S:Fault>

</S:Body>

</S:Envelope>



Can some help me to understand how do that ?

Other question : if have the xml file content in a variable .. can I use
SQL to parse Xml ?



Thanks in advance