Topic
  • 7 replies
  • Latest Post - ‏2012-05-03T18:43:03Z by ElinaHazaran
RichardTT
RichardTT
4 Posts

Pinned topic DB2 pureXML question

‏2011-04-07T15:04:03Z |
Hi,

I am new to pureXML & XQuery. I got an error when I run query as following, does anyone know the reason? Thanks in advance.

The query I used as following on DB2 9.7 table:

select xmlquery('$c/session/data/policy/QuoteNumber'
passing DO_HST_XML_DATA as "c")
from DO_HST_HISTORY_DB_TABLE
where DO_HST_DCT_HISTORY_ID < 300

Note:
DO_HST_HISTORY_DB_TABLE: table name
DO_HST_XML_DATA: column with XML file in the table
DO_HST_DCT_HISTORY_ID : regular column, integer type

Got error as following:
The XQuery expression cannot be processed.

User Response:

Modify the expression to ensure that each intermediate step in an
XQuery expression returns a sequence of nodes.

sqlcode : -16011

sqlstate : 10507
Updated on 2012-05-03T18:43:03Z at 2012-05-03T18:43:03Z by ElinaHazaran
  • MatthiasNicola
    MatthiasNicola
    47 Posts

    Re: DB2 pureXML question

    ‏2011-04-08T06:14:37Z  
    Hi Richard,

    the error that you get is SQL16011N The result of an intermediate step expression in an XQuery path expression contains an atomic value..

    It means, roughly speaking, that an XQuery expression first produces an atomic value, such as an integer or a text value, and then tries to use that value as the starting point for further navigation with XPath. However, an atomic is already atomic and there is no more XML structure to navigate into. Hence the error.

    However, I don't quite see how your query can possibly cause the error to happen. So, let's dig a little bit deeper and try to narrow things down. Could you run the following three queries (with shortened XQuery expressions) and see if any of them fail with the same error?:

    Q1:
    select xmlquery('$c/session/data/policy' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Q2:
    select xmlquery('$c/session/data' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Q3:
    select xmlquery('$c/session' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Next: can you tell whether the error happens for any XML document in the table, or only when specific documents are accessed? In other words, what happens if you change the WHERE clause to target different documents?

    If you can narrow this problem down to specific rows (documents) that would be helpful. It would be good to see an XML document for which you get this error. if that is an option, note that you can attach text files when you post here.

    Thanks,

    • Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • RichardTT
    RichardTT
    4 Posts

    Re: DB2 pureXML question

    ‏2011-04-08T14:54:25Z  
    Hi Richard,

    the error that you get is SQL16011N The result of an intermediate step expression in an XQuery path expression contains an atomic value..

    It means, roughly speaking, that an XQuery expression first produces an atomic value, such as an integer or a text value, and then tries to use that value as the starting point for further navigation with XPath. However, an atomic is already atomic and there is no more XML structure to navigate into. Hence the error.

    However, I don't quite see how your query can possibly cause the error to happen. So, let's dig a little bit deeper and try to narrow things down. Could you run the following three queries (with shortened XQuery expressions) and see if any of them fail with the same error?:

    Q1:
    select xmlquery('$c/session/data/policy' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Q2:
    select xmlquery('$c/session/data' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Q3:
    select xmlquery('$c/session' passing DO_HST_XML_DATA as "c")
    from DO_HST_HISTORY_DB_TABLE
    where DO_HST_DCT_HISTORY_ID < 300

    Next: can you tell whether the error happens for any XML document in the table, or only when specific documents are accessed? In other words, what happens if you change the WHERE clause to target different documents?

    If you can narrow this problem down to specific rows (documents) that would be helpful. It would be good to see an XML document for which you get this error. if that is an option, note that you can attach text files when you post here.

    Thanks,

    • Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks a lot Matthias for quick reply. I narrow things down with your three queries. It got same error message and finally I found the XML file format in DB2 CLOB was not correct. They are all wrong! I will get the correct XML clob next week and see what happens.
    Thanks again!

    Richard
  • MatthiasNicola
    MatthiasNicola
    47 Posts

    Re: DB2 pureXML question

    ‏2011-04-08T16:00:47Z  
    • RichardTT
    • ‏2011-04-08T14:54:25Z
    Thanks a lot Matthias for quick reply. I narrow things down with your three queries. It got same error message and finally I found the XML file format in DB2 CLOB was not correct. They are all wrong! I will get the correct XML clob next week and see what happens.
    Thanks again!

    Richard
    Ok, let me know what you find.

    Just to clarify: to run SQL/XML or XQuery, you need to use a column of type of XML, not of type CLOB. Indeed using a CLOB column instead of an XML column would cause the error SQL16011N that you got.

    Here is how you can migrate from a LOB column to an XML column:
    http://nativexmldatabase.com/2010/10/03/how-to-migrate-xml-from-lob-to-xml-columns/


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • RichardTT
    RichardTT
    4 Posts

    Re: DB2 pureXML question

    ‏2011-04-15T15:31:57Z  
    Ok, let me know what you find.

    Just to clarify: to run SQL/XML or XQuery, you need to use a column of type of XML, not of type CLOB. Indeed using a CLOB column instead of an XML column would cause the error SQL16011N that you got.

    Here is how you can migrate from a LOB column to an XML column:
    http://nativexmldatabase.com/2010/10/03/how-to-migrate-xml-from-lob-to-xml-columns/


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks a lot Matthias! You are right, our column is CLOB type. It's got loaded from source by ETL. Our ETL developer said he defined XML type in ETL tool, then loaded into DB2, the column changed to CLOB type automatically. Do you have any suggestion on this?

    Thanks again,

    Richard
  • MatthiasNicola
    MatthiasNicola
    47 Posts

    Re: DB2 pureXML question

    ‏2011-04-15T20:16:24Z  
    • RichardTT
    • ‏2011-04-15T15:31:57Z
    Thanks a lot Matthias! You are right, our column is CLOB type. It's got loaded from source by ETL. Our ETL developer said he defined XML type in ETL tool, then loaded into DB2, the column changed to CLOB type automatically. Do you have any suggestion on this?

    Thanks again,

    Richard
    Hi Richard,

    it's hard to comment on this without further details. For example, which ETL tool was used how was the ETL job implemented?

    However, it sounds very unlikely that an ETL tool would magically change an existing XML column to a CLOB column. For example, it is not possible in DB2 to use an "alter table ... alter column..." command to change the data type of an XML column. You would need to drop the XML column and then add the CLOB column, and that would delete all existing data in the XML column unless you export it first. So, it takes some very explicit and intentional action to switch from an XML column to a CLOB column. It doesn't happen by chance.

    Maybe the column was defined (accidentally) as CLOB in the first place? This would be a more likely explanation for what you are seeing.

    My suggestion: work with your ETL developer and try this again. Define a table with an XML column, make sure that you see the CREATE TABLE statement and that you run a DESCRIBE TABLE command to confirm the column types. Then run the ETL job again and describe the table again.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • RichardTT
    RichardTT
    4 Posts

    Re: DB2 pureXML question

    ‏2011-04-18T13:53:45Z  
    Hi Richard,

    it's hard to comment on this without further details. For example, which ETL tool was used how was the ETL job implemented?

    However, it sounds very unlikely that an ETL tool would magically change an existing XML column to a CLOB column. For example, it is not possible in DB2 to use an "alter table ... alter column..." command to change the data type of an XML column. You would need to drop the XML column and then add the CLOB column, and that would delete all existing data in the XML column unless you export it first. So, it takes some very explicit and intentional action to switch from an XML column to a CLOB column. It doesn't happen by chance.

    Maybe the column was defined (accidentally) as CLOB in the first place? This would be a more likely explanation for what you are seeing.

    My suggestion: work with your ETL developer and try this again. Define a table with an XML column, make sure that you see the CREATE TABLE statement and that you run a DESCRIBE TABLE command to confirm the column types. Then run the ETL job again and describe the table again.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks Matthias! We load table to DB2 from SQL table. I think you are right, we need to check the source. I will work with ETL developer to figure out. (Use IBM ETL tool).
    And good new is, I created temp table with XML column and my pureXML query works! Thanks again.

    Richard
  • ElinaHazaran
    ElinaHazaran
    1 Post

    Re: DB2 pureXML question

    ‏2012-05-03T18:43:03Z  
    Ok, let me know what you find.

    Just to clarify: to run SQL/XML or XQuery, you need to use a column of type of XML, not of type CLOB. Indeed using a CLOB column instead of an XML column would cause the error SQL16011N that you got.

    Here is how you can migrate from a LOB column to an XML column:
    http://nativexmldatabase.com/2010/10/03/how-to-migrate-xml-from-lob-to-xml-columns/


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi, I found this thread when I was looking for a solution to the same problem described above. The only difference is that I have a CLOB column on purpose, basically the aim of my project is to test all the possibilities of querying XML data in DB2.

    So, is this possible to cast the CLOB column to XML inside some SQL/XML statement? For example, in Oracle it will look like this: SELECT XQuery('xquery' PASSING XMLTYPE(clob_column)) FROM table. Is there any equivalent in DB2 with such an embedded conversion?

    Thanks in advance