Topic
  • 5 replies
  • Latest Post - ‏2012-02-07T09:33:21Z by KKCrazy
KKCrazy
KKCrazy
5 Posts

Pinned topic Find the no of Elements in an XML

‏2012-02-05T13:06:36Z |
Hi,

Can someone please help me on the below issues for the given XML. This xml comes as an input and i need to validate this.

Sample XML

<main>
<info>
12345
</info>
<info>
3456
</info>
<info>
67686
</info>
</main>

1. How to validate whether the XML contains only one <main> element
2. How to validate whether atleast one <info> element exists and all <info> elements are direct descendants of <main>

I am a bit new to this DB2 PURE XML and need some help on this. Appreciate any help on this.

Thanks
Prasanna
Updated on 2012-02-07T09:33:21Z at 2012-02-07T09:33:21Z by KKCrazy
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Find the no of Elements in an XML

    ‏2012-02-05T21:07:21Z  
    Such validation can be done with an XML Schema in which you can define the constraints that you want to impose on the documents. If you are not yet familiar with XML Schema, you will need to spend some time to go through tutorials. There are many XML Schema tutorials on the web:
    http://www.w3schools.com/schema/default.asp
    http://www.xfront.com/xml-schema.html
    etc.

    The other option is to run queries on the XML to count the occurrences of certain element and the check there position in the document. For example, the following query searches the entire document for "main" elements and counts the number of occurrences.

    SELECT xmlquery('count( $DOC//main )')
    FROM test;

    You can use similar query concepts for your second problem. Give it a try.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • KKCrazy
    KKCrazy
    5 Posts

    Re: Find the no of Elements in an XML

    ‏2012-02-06T02:55:25Z  
    Such validation can be done with an XML Schema in which you can define the constraints that you want to impose on the documents. If you are not yet familiar with XML Schema, you will need to spend some time to go through tutorials. There are many XML Schema tutorials on the web:
    http://www.w3schools.com/schema/default.asp
    http://www.xfront.com/xml-schema.html
    etc.

    The other option is to run queries on the XML to count the occurrences of certain element and the check there position in the document. For example, the following query searches the entire document for "main" elements and counts the number of occurrences.

    SELECT xmlquery('count( $DOC//main )')
    FROM test;

    You can use similar query concepts for your second problem. Give it a try.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks for the information. I need to go through all these provided sites to get good knowledge on DB2 PURE XML.

    I tried the below two statements in a stored procedure,

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)) AS INTEGER);--

    SELECT XMLQUERY('count( $DOC//main )') INTO MAINCOUNT
    FROM BOA.TEST WHERE XMLEXISTS('$DOC/main');

    But it gives me an error saying that

    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55
    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55

    Any help on this

    Thanks
    Prasanna
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Find the no of Elements in an XML

    ‏2012-02-06T07:43:44Z  
    • KKCrazy
    • ‏2012-02-06T02:55:25Z
    Thanks for the information. I need to go through all these provided sites to get good knowledge on DB2 PURE XML.

    I tried the below two statements in a stored procedure,

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)) AS INTEGER);--

    SELECT XMLQUERY('count( $DOC//main )') INTO MAINCOUNT
    FROM BOA.TEST WHERE XMLEXISTS('$DOC/main');

    But it gives me an error saying that

    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55
    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55

    Any help on this

    Thanks
    Prasanna
    One recommendation is to test such queries on their own (e.g. in the DB2 Command Line Processor) before using them in a stored procedure. For example, note that the following two queries can return very different results:

    xquery db2-fn:xmlcolumn("TEST.DOC")//count(main);

    xquery count( db2-fn:xmlcolumn("TEST.DOC")//main);
    Also note the difference between these two queries:

    xquery db2-fn:xmlcolumn("TEST.DOC")//count(name);

    xquery count( db2-fn:xmlcolumn("TEST.DOC")//name);
    The error message that you posted refers to an object called "CTEXT", which may occur elsewhere in the procedure and may be involved in the error.

    Also note that the two statements that you tried use MAINCOUNT with different data types. In your first statement, i.e. SET MAINCOUNT = XMLCAST(....), you try to assign an integer value to MAINCOUNT, and hence MAINCOUNT must be declared as type integer.

    In your second statement you try to assign the result of XMLQUERY to MAINCOUNT, and the type of XMLQUERY is always XML. Hence, MAINCOUNT needs to be declared as type XML for this to work.
    Check how MAINCOUNT is defined in your procedure.

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Find the no of Elements in an XML

    ‏2012-02-06T07:48:44Z  
    • KKCrazy
    • ‏2012-02-06T02:55:25Z
    Thanks for the information. I need to go through all these provided sites to get good knowledge on DB2 PURE XML.

    I tried the below two statements in a stored procedure,

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)) AS INTEGER);--

    SELECT XMLQUERY('count( $DOC//main )') INTO MAINCOUNT
    FROM BOA.TEST WHERE XMLEXISTS('$DOC/main');

    But it gives me an error saying that

    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55
    A value is not compatible with the data type of its assignment target. Target name is "CTEXT".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.12.55

    Any help on this

    Thanks
    Prasanna
    And there is another problem with this statement:

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)) AS INTEGER);

    There is a closing single quote missing in the XMLQUERY function. Here it is with the missing quote added:

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)') AS INTEGER);


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • KKCrazy
    KKCrazy
    5 Posts

    Re: Find the no of Elements in an XML

    ‏2012-02-07T09:33:21Z  
    And there is another problem with this statement:

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)) AS INTEGER);

    There is a closing single quote missing in the XMLQUERY function. Here it is with the missing quote added:

    SET MAINCOUNT = XMLCAST( XMLQUERY('db2-fn:xmlcolumn("BOA.TEST.DOC")//count(main)') AS INTEGER);


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    You are right Matthias. The issue CTEXT is from the earlier query.

    And the queries suggested worked for me again.

    Thank you very much for your help.

    I am learning new things everyday on DB2.

    Thanks
    Prasanna