XMLQUERY to retrieve an attribute of all nodes

Steven Goedertier

XMLQUERY to retrieve an attribute of all nodes

Hello,

I've been trying to get the following XMLQUERY to work:

SELECT XMLQUERY('//[login to unmask email]' PASSING XMLPARSE(LG.MESSAGE)) as XML_RESULT
FROM <schema>.<log_table> LG
WHERE id = 5;

to get a list of all the severity attributes, but I get the following return:

THE SEQUENCE TO BE SERIALIZED CONTAINS AN ITEM THAT IS AN ATTRIBUTE NODE.  
ERROR QNAME = err:SENR0001. SQLCODE=-16075, SQLSTATE=2200W, DRIVER=3.72.24

 

In attachment I'll provide the result of 

SELECT XMLQUERY('/' PASSING XMLPARSE(LG.MESSAGE)) as XML_RESULT
FROM <schema>.<log_table> LG
WHERE id = 5;

(without the @severity)

to give a sample of the xml I'm trying to go through.

What am I doing wrong?

Steven Goedertier

Db2 Systems DBA
Colruyt Group 

Attachments

  • xml_sample.txt (5.7k)

Philip Nelson

XMLQUERY to retrieve an attribute of all nodes
(in response to Steven Goedertier)
Steven
I think (from memory) you have to use the data() function to get the
attribute value rather than the attribute node.

Philip

On Wed, 13 Jun 2018, 10:44 Steven Goedertier, <[login to unmask email]> wrote:

> Hello,
>
> I've been trying to get the following XMLQUERY to work:
>
> SELECT XMLQUERY('//[login to unmask email]' PASSING XMLPARSE(LG.MESSAGE)) as XML_RESULT
> FROM . LG
> WHERE id = 5;
>
> to get a list of all the severity attributes, but I get the following
> return:
>
> THE SEQUENCE TO BE SERIALIZED CONTAINS AN ITEM THAT IS AN ATTRIBUTE NODE.
> ERROR QNAME = err:SENR0001. SQLCODE=-16075, SQLSTATE=2200W, DRIVER=3.72.24
>
>
>
> In attachment I'll provide the result of
>
> SELECT XMLQUERY('/' PASSING XMLPARSE(LG.MESSAGE)) as XML_RESULT
> FROM . LG
> WHERE id = 5;
>
> (without the @severity)
>
> to give a sample of the xml I'm trying to go through.
>
> What am I doing wrong?
>
> Steven Goedertier
>
> Db2 Systems DBA
> Colruyt Group
>
> -----End Original Message-----
>

Peter Vanroose

Re: XMLQUERY to retrieve an attribute of all nodes
(in response to Steven Goedertier)

Steven,

For an element node, you could have appended "/text()" (without the quotes) to get just the text content of that node.

For an attribute node there is no /text(), unfortunately. Because it *is* already text (but Db2 does not think so, apparently...)

You could use the following workaround, though:

XMLQUERY('//message/concat(@severity,"")' PASSING XMLPARSE(LG.MESSAGE))

which forces the attribute node into text "mode" (since the concat function returns text, not a node).
So this is a kind of an implicit cast.

In Reply to Steven Goedertier:

[...]

SELECT XMLQUERY('//[login to unmask email]' PASSING XMLPARSE(LG.MESSAGE)) as XML_RESULT
FROM <schema>.<log_table> LG
WHERE id = 5;

to get a list of all the severity attributes, but I get the following return:

THE SEQUENCE TO BE SERIALIZED CONTAINS AN ITEM THAT IS AN ATTRIBUTE NODE.  
ERROR QNAME = err:SENR0001. SQLCODE=-16075, SQLSTATE=2200W, DRIVER=3.72.24


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Edited By:
Peter Vanroose[Organization Members] @ Jun 25, 2018 - 04:43 PM (Europe/Brussels)

Steven Goedertier

Re: XMLQUERY to retrieve an attribute of all nodes
(in response to Peter Vanroose)

Thx Peter,

that works...

I also got a tip from Phil to use XMLTABLE function that also works:

SELECT X.*
from idbad001.idaa_admin_log LG,
XMLTABLE(XMLNAMESPACES('http://www.ibm.com/xmlns/prod/dwa/2011' AS "dwa"),
'$m/*/message' PASSING XMLPARSE(LG.MESSAGE) as "m"
COLUMNS "sev" VARCHAR(500) PATH '@severity') as X

Thanks to you both!

Steven Goedertier

Db2 Systems DBA
Colruyt Group