Topic
  • 10 replies
  • Latest Post - ‏2011-08-16T14:08:01Z by VV4H_mario_fernandes
VV4H_mario_fernandes
43 Posts

Pinned topic fn:tokenize - convert tokens to rows

‏2011-08-08T19:11:58Z |
Hi

I would like to convert each token returned by the fn:tokenize to a new row.

My Sql is as follows
SELECT *
FROM KASPER.XML_CLIENT C,
XMLTABLE('$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName' PASSING C.XML_CLIENT AS "d"
COLUMNS
legalName varchar(5000) PATH 'fn:tokenize(text(),"/")'

) AS X ;
However I am getting the following error.
An error occurred while processing the results. - An expression of data type "( item(), item()+ )" cannot be used when the data type "VARCHAR_5000" is expected in the context. Error QName=err:XPTY0004.. SQLCODE=-16003, SQLSTATE=10507, DRIVER=3.61.65
Updated on 2011-08-16T14:08:01Z at 2011-08-16T14:08:01Z by VV4H_mario_fernandes
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-08T22:34:51Z  
    Hi Mario,

    try this:

    
    SELECT * FROM KASPER.XML_CLIENT C, XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") ' PASSING C.XML_CLIENT AS 
    "d" COLUMNS legalName varchar(5000) PATH 
    '.') AS X ;
    


    This should return one row for each token produced by the fn:tokenize function. The trick here is to make the fn:tokenize function part of the row-generating expression in the XMLTABLE function.

    Does this solve your problem?

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-10T13:41:16Z  
    Hi Mario,

    try this:

    <pre class="jive-pre"> SELECT * FROM KASPER.XML_CLIENT C, XMLTABLE( '$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") ' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(5000) PATH '.') AS X ; </pre>

    This should return one row for each token produced by the fn:tokenize function. The trick here is to make the fn:tokenize function part of the row-generating expression in the XMLTABLE function.

    Does this solve your problem?

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias

    Thanks for the reply , however your solution does not work.
    The error is given below.

    SELECT *
    FROM KASPER.XML_CLIENT C,
    XMLTABLE('$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") '
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(5000) PATH '.') AS X

    An error occurred while processing the results. - The context item in an axis step must be an XQuery node. Error QName=err:XPTY0020.. SQLCODE=-16012, SQLSTATE=10507, DRIVER=3.61.65
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-10T15:34:52Z  
    Hi Matthias

    Thanks for the reply , however your solution does not work.
    The error is given below.

    SELECT *
    FROM KASPER.XML_CLIENT C,
    XMLTABLE('$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") '
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(5000) PATH '.') AS X

    An error occurred while processing the results. - The context item in an axis step must be an XQuery node. Error QName=err:XPTY0020.. SQLCODE=-16012, SQLSTATE=10507, DRIVER=3.61.65
    Ahhhh, yes. I didn't think of that. fn:tokenize produces atomic values but the output from the row-generating expression must be a sequence of nodes, such as XML elements.
    One solution is to construct an element from each token that is produced, like this:

    
    CREATE TABLE kasper(xml_client XML);   INSERT INTO kasper VALUES(
    ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>AAA/BBB/CCC</legalName> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema>
    ');   SELECT legalName FROM kasper C, XMLTABLE(
    'for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")  
    
    return <token>
    {$tok
    }</token>
    '  PASSING C.XML_CLIENT AS 
    "d" COLUMNS legalName varchar(50) PATH 
    '.') AS X ;     LEGALNAME -------------------------------------------------- AAA BBB CCC   3 record(s) selected.   db2 =>
    

    BTW, the same result can be produced with the following XQuery:
    
    xquery db2-fn:xmlcolumn(
    "KASPER.XML_CLIENT")/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),
    "/");
    


    The only difference is that this XQuery produces the three values AAA, BBB, and CCC in a column of type XML rather than VARCHAR, but that may not matter because the application can still fetch these values into character variables.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-10T16:34:38Z  
    Ahhhh, yes. I didn't think of that. fn:tokenize produces atomic values but the output from the row-generating expression must be a sequence of nodes, such as XML elements.
    One solution is to construct an element from each token that is produced, like this:

    <pre class="jive-pre"> CREATE TABLE kasper(xml_client XML); INSERT INTO kasper VALUES( ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>AAA/BBB/CCC</legalName> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema> '); SELECT legalName FROM kasper C, XMLTABLE( 'for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") return <token> {$tok }</token> ' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(50) PATH '.') AS X ; LEGALNAME -------------------------------------------------- AAA BBB CCC 3 record(s) selected. db2 => </pre>
    BTW, the same result can be produced with the following XQuery:
    <pre class="jive-pre"> xquery db2-fn:xmlcolumn( "KASPER.XML_CLIENT")/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(), "/"); </pre>

    The only difference is that this XQuery produces the three values AAA, BBB, and CCC in a column of type XML rather than VARCHAR, but that may not matter because the application can still fetch these values into character variables.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Mattias

    Now I am getting this error.
    Any ideas?

    SELECT legalName
    FROM kasper.XML_CLIENT C,
    XMLTABLE('for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")
    return <token>{$tok}</token>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH '.') AS X

    jcct4102611213http://3.61.65 Bug check exception thrown due to an internal JCC error. Please contact support. Message text: Error happened on server. Severity code 8. No exception code returned from server. ERRORCODE=-4228, SQLSTATE=null

    Failed queries => 1

    Total execution time => 0 ms
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-10T19:14:53Z  
    Hi Mattias

    Now I am getting this error.
    Any ideas?

    SELECT legalName
    FROM kasper.XML_CLIENT C,
    XMLTABLE('for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")
    return <token>{$tok}</token>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH '.') AS X

    jcct4102611213http://3.61.65 Bug check exception thrown due to an internal JCC error. Please contact support. Message text: Error happened on server. Severity code 8. No exception code returned from server. ERRORCODE=-4228, SQLSTATE=null

    Failed queries => 1

    Total execution time => 0 ms
    Hi Matthias

    I have solved the issue. The column size was too small.
    SELECT legalName FROM kasper.XML_CLIENT C, XMLTABLE('for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") return <token>{$tok}</token>' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(50) PATH '.') AS X

    LEGALNAME

    DAYTON PLAZA, LLC

    897438 ALBERTA LTD

    SQL16061N The value "CIRCLE ..."

    cannot be constructed as, or cast (using an implicit or explicit cast) to the

    data type "VARCHAR_50". Error QName=err:FORG0001. SQLSTATE=10608
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-10T19:33:52Z  
    Ahhhh, yes. I didn't think of that. fn:tokenize produces atomic values but the output from the row-generating expression must be a sequence of nodes, such as XML elements.
    One solution is to construct an element from each token that is produced, like this:

    <pre class="jive-pre"> CREATE TABLE kasper(xml_client XML); INSERT INTO kasper VALUES( ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>AAA/BBB/CCC</legalName> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema> '); SELECT legalName FROM kasper C, XMLTABLE( 'for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") return <token> {$tok }</token> ' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(50) PATH '.') AS X ; LEGALNAME -------------------------------------------------- AAA BBB CCC 3 record(s) selected. db2 => </pre>
    BTW, the same result can be produced with the following XQuery:
    <pre class="jive-pre"> xquery db2-fn:xmlcolumn( "KASPER.XML_CLIENT")/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(), "/"); </pre>

    The only difference is that this XQuery produces the three values AAA, BBB, and CCC in a column of type XML rather than VARCHAR, but that may not matter because the application can still fetch these values into character variables.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Mathias

    I have one more question.
    I have been trying very hard to access other elements in the xml structure, but I am getting NULL value
    Can you show me how to access the countryOfRisk element below?
    I have tried different paths but none works.

    CREATE TABLE kasper(xml_client XML);

    INSERT INTO kasper VALUES('
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client>
    <legalEntity>

    <legalName>AAA/BBB/CCC</legalName>
    <countryOfRisk>Canada</countryOfRisk>

    </legalEntity>
    </client>
    </kasperClient>
    </TDSClient>
    </kasperSchema>');
    SELECT legalName
    FROM kasper C,
    XMLTABLE('for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")
    return <token>{$tok}</token>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH '.') AS X ;
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-11T01:58:35Z  
    Hi Mathias

    I have one more question.
    I have been trying very hard to access other elements in the xml structure, but I am getting NULL value
    Can you show me how to access the countryOfRisk element below?
    I have tried different paths but none works.

    CREATE TABLE kasper(xml_client XML);

    INSERT INTO kasper VALUES('
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client>
    <legalEntity>

    <legalName>AAA/BBB/CCC</legalName>
    <countryOfRisk>Canada</countryOfRisk>

    </legalEntity>
    </client>
    </kasperClient>
    </TDSClient>
    </kasperSchema>');
    SELECT legalName
    FROM kasper C,
    XMLTABLE('for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")
    return <token>{$tok}</token>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH '.') AS X ;
    Hi Mario,

    I assume you want to get a result set such as the following:

    
    LEGALNAME                                          COUNTRY -------------------------------------------------- ---------------------------- AAA                                                Canada BBB                                                Canada CCC                                                Canada   3 record(s) selected.
    


    In other words, the country gets repeated for each token of the legal name. If this is what you want, here is an example with two queries that both produce the same result set:

    
    DROP TABLE kasper; CREATE TABLE kasper(xml_client XML);   INSERT INTO kasper VALUES(
    ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>AAA/BBB/CCC</legalName> <countryOfRisk>Canada</countryOfRisk> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema>
    ');   INSERT INTO kasper VALUES(
    ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>XXX/YYY/ZZZ</legalName> <countryOfRisk>US</countryOfRisk> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema>
    ');   SELECT legalName, country FROM kasper C, XMLTABLE(
    'for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/")  
    
    return <token>
    {$tok
    }</token>
    '  PASSING C.XML_CLIENT AS 
    "d" COLUMNS legalName varchar(50) PATH 
    '.') AS X, XMLTABLE(
    '$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/countryOfRisk' PASSING C.XML_CLIENT AS 
    "d" COLUMNS country varchar(50) PATH 
    '.') AS Y           ;     SELECT legalName, country FROM kasper C, XMLTABLE(
    'for $le  in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity 
    
    for $tok in $le/legalName/fn:tokenize(text(),
    "/") 
    
    return <out><token>
    {$tok
    }</token>
    {$le/countryOfRisk
    }</out>
    '  PASSING C.XML_CLIENT AS 
    "d" COLUMNS legalName varchar(50) PATH 
    'token', country   varchar(50) PATH 
    'countryOfRisk') AS X ;
    

    Both queries return the following result:

    
    LEGALNAME                                          COUNTRY -------------------------------------------------- ----------------------------- AAA                                                Canada BBB                                                Canada CCC                                                Canada XXX                                                US YYY                                                US ZZZ                                                US   6 record(s) selected.
    


    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-12T14:42:55Z  
    Hi Mario,

    I assume you want to get a result set such as the following:

    <pre class="jive-pre"> LEGALNAME COUNTRY -------------------------------------------------- ---------------------------- AAA Canada BBB Canada CCC Canada 3 record(s) selected. </pre>

    In other words, the country gets repeated for each token of the legal name. If this is what you want, here is an example with two queries that both produce the same result set:

    <pre class="jive-pre"> DROP TABLE kasper; CREATE TABLE kasper(xml_client XML); INSERT INTO kasper VALUES( ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>AAA/BBB/CCC</legalName> <countryOfRisk>Canada</countryOfRisk> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema> '); INSERT INTO kasper VALUES( ' <kasperSchema> <TDSClient> <kasperClient> <client> <legalEntity> <legalName>XXX/YYY/ZZZ</legalName> <countryOfRisk>US</countryOfRisk> </legalEntity> </client> </kasperClient> </TDSClient> </kasperSchema> '); SELECT legalName, country FROM kasper C, XMLTABLE( 'for $tok in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity/legalName/fn:tokenize(text(),"/") return <token> {$tok }</token> ' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(50) PATH '.') AS X, XMLTABLE( '$d/kasperSchema/TDSClient/kasperClient/client/legalEntity/countryOfRisk' PASSING C.XML_CLIENT AS "d" COLUMNS country varchar(50) PATH '.') AS Y ; SELECT legalName, country FROM kasper C, XMLTABLE( 'for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity for $tok in $le/legalName/fn:tokenize(text(), "/") return <out><token> {$tok }</token> {$le/countryOfRisk }</out> ' PASSING C.XML_CLIENT AS "d" COLUMNS legalName varchar(50) PATH 'token', country varchar(50) PATH 'countryOfRisk') AS X ; </pre>
    Both queries return the following result:

    <pre class="jive-pre"> LEGALNAME COUNTRY -------------------------------------------------- ----------------------------- AAA Canada BBB Canada CCC Canada XXX US YYY US ZZZ US 6 record(s) selected. </pre>

    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Matthias

    Thank you for your prompt reply. The solution below is correct.

    I have one questions about the second sql query.
    How would I access the attribute id shown in the xml below using the 2'nd query
    INSERT INTO kasper VALUES('
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client id= "19724">
    <legalEntity>
    <legalName>AAA/BBB/CCC</legalName>
    <countryOfRisk>Canada</countryOfRisk>
    </legalEntity>
    </client>
    </kasperClient>
    </TDSClient>
    </kasperSchema>');

    SELECT legalName, country
    FROM kasper C,
    XMLTABLE('for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity
    for $tok in $le/legalName/fn:tokenize(text(),"/")
    return <out><token>{$tok}</token>{$le/countryOfRisk}</out>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH 'token',
    country varchar(50) PATH 'countryOfRisk') AS X
    ;
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-13T03:13:45Z  
    Hi Matthias

    Thank you for your prompt reply. The solution below is correct.

    I have one questions about the second sql query.
    How would I access the attribute id shown in the xml below using the 2'nd query
    INSERT INTO kasper VALUES('
    <kasperSchema>
    <TDSClient>
    <kasperClient>
    <client id= "19724">
    <legalEntity>
    <legalName>AAA/BBB/CCC</legalName>
    <countryOfRisk>Canada</countryOfRisk>
    </legalEntity>
    </client>
    </kasperClient>
    </TDSClient>
    </kasperSchema>');

    SELECT legalName, country
    FROM kasper C,
    XMLTABLE('for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity
    for $tok in $le/legalName/fn:tokenize(text(),"/")
    return <out><token>{$tok}</token>{$le/countryOfRisk}</out>'
    PASSING C.XML_CLIENT AS "d"
    COLUMNS
    legalName varchar(50) PATH 'token',
    country varchar(50) PATH 'countryOfRisk') AS X
    ;
    Hi Mario,

    the second option of writing this query works on the principle that the row-generating expression is a FLWOR expression in which the "return" clause constructs an XML structure that is input to the column-generating expressions. With this approach, anything that you want to return as an XMLTABLE column must be included in the constructed XML in the return clause:

    
    SELECT cid, legalName, country FROM kasper C, XMLTABLE(
    'for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity 
    
    for $tok in $le/legalName/fn:tokenize(text(),
    "/") 
    
    return <out>
    {$le/../@id
    }<token>
    {$tok
    }</token>
    {$le/countryOfRisk
    }</out>
    ' PASSING C.XML_CLIENT AS 
    "d" COLUMNS cid        integer     PATH 
    '@id', legalName  varchar(50) PATH 
    'token', country    varchar(50) PATH 
    'countryOfRisk') AS X ;   CID LEGALNAME                         COUNTRY ----------- --------------------------------- --------------------------------- 19724 AAA                               Canada 19724 BBB                               Canada 19724 CCC                               Canada   3 record(s) selected.
    

    Maybe the following equivalent query makes it even clearer:

    
    SELECT cid, legalName, country FROM kasper C, XMLTABLE(
    'for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity 
    
    for $tok in $le/legalName/fn:tokenize(text(),
    "/") 
    
    return document
    {<out>
    {$le/../@id
    }<token>
    {$tok
    }</token>
    {$le/countryOfRisk
    }</out>
    }
    ' PASSING C.XML_CLIENT AS 
    "d" COLUMNS cid        integer     PATH 
    'out/@id', legalName  varchar(50) PATH 
    'out/token', country    varchar(50) PATH 
    'out/countryOfRisk') AS X ;   CID         LEGALNAME                         COUNTRY ----------- --------------------------------- --------------------------------- 19724 AAA                               Canada 19724 BBB                               Canada 19724 CCC                               Canada   3 record(s) selected.
    


    In this query, the function document{} constructs an XML document node on top of the constructed XML, so the navigation (the path expressions) in the column definitions are maybe more intuitive.

    In general, this approach of constructing XML in the row-generating expression can be very useful to write even tricky XMLTABLE queries, but can be cumbersome if the number of columns that you want to generate is very large.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: fn:tokenize - convert tokens to rows

    ‏2011-08-16T14:08:01Z  
    Hi Mario,

    the second option of writing this query works on the principle that the row-generating expression is a FLWOR expression in which the "return" clause constructs an XML structure that is input to the column-generating expressions. With this approach, anything that you want to return as an XMLTABLE column must be included in the constructed XML in the return clause:

    <pre class="jive-pre"> SELECT cid, legalName, country FROM kasper C, XMLTABLE( 'for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity for $tok in $le/legalName/fn:tokenize(text(), "/") return <out> {$le/../@id }<token> {$tok }</token> {$le/countryOfRisk }</out> ' PASSING C.XML_CLIENT AS "d" COLUMNS cid integer PATH '@id', legalName varchar(50) PATH 'token', country varchar(50) PATH 'countryOfRisk') AS X ; CID LEGALNAME COUNTRY ----------- --------------------------------- --------------------------------- 19724 AAA Canada 19724 BBB Canada 19724 CCC Canada 3 record(s) selected. </pre>
    Maybe the following equivalent query makes it even clearer:

    <pre class="jive-pre"> SELECT cid, legalName, country FROM kasper C, XMLTABLE( 'for $le in $d/kasperSchema/TDSClient/kasperClient/client/legalEntity for $tok in $le/legalName/fn:tokenize(text(), "/") return document {<out> {$le/../@id }<token> {$tok }</token> {$le/countryOfRisk }</out> } ' PASSING C.XML_CLIENT AS "d" COLUMNS cid integer PATH 'out/@id', legalName varchar(50) PATH 'out/token', country varchar(50) PATH 'out/countryOfRisk') AS X ; CID LEGALNAME COUNTRY ----------- --------------------------------- --------------------------------- 19724 AAA Canada 19724 BBB Canada 19724 CCC Canada 3 record(s) selected. </pre>

    In this query, the function document{} constructs an XML document node on top of the constructed XML, so the navigation (the path expressions) in the column definitions are maybe more intuitive.

    In general, this approach of constructing XML in the row-generating expression can be very useful to write even tricky XMLTABLE queries, but can be cumbersome if the number of columns that you want to generate is very large.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks a lot for all the answers.