Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Unanswered: XML extender tablefunctions

    Hi,

    I have made a table with one xml column of type XMLCLOB and stored an xml document in it. The xml document has multi occurance = YES for many of its elements. I want to extract the informasjon from the xml document which is stored in the xml column and I know that I have to use Table functions like ExtractIntegers. Is any body know the syntax for this? And anybody has an example for this?
    The platform we are using is DB2 UDB 7.1 for OS/390.

    Brgds
    THava

  2. #2
    Join Date
    Mar 2003
    Posts
    33

    Re: XML extender tablefunctions

    I think it's best if you look at this xml-extender guide:

    http://www-3.ibm.com/software/data/d...ETRIEVEELEMENT



    Good luck.
    -r-

  3. #3
    Join Date
    Aug 2003
    Posts
    5
    I have done it but not for any help. It only describe how we can retrive informasjon that stored in XMLFILE data type.

  4. #4
    Join Date
    Mar 2003
    Posts
    33
    The link I send You tells how to retrieve content and values of elements and attributes from xmlobj. The xmlobj can be XMLVARCHAR, XMLCLOB or XMLFILE. So you can use this function with yours xmlclob-type column. The syntax is:

    db2xml.extractretrieved_datatype(xmlobj, path)


    Where:

    retrieved_datatype
    Is the data type that is returned from the extracting function; it can be one of the following types:

    * INTEGER
    * SMALLINT
    * DOUBLE
    * REAL
    * CHAR
    * VARCHAR
    * CLOB
    * DATE
    * TIME
    * TIMESTAMP
    * FILE

    xmlobj
    Is the name of the XML column from which the element or attribute is to be extracted. This column must be defined as one of the following XML user-defined types:

    * XMLVARCHAR
    * XMLCLOB as LOCATOR
    * XMLFILE

    path
    Is the location path of the element or attribute in the XML document (such as /Order/Customer/Name). See Location path for location path syntax.


    EXAMPLE:

    SELECT db2xml.extractVarchar(Order, '/Order/Customer/Name') from sales_order_view

    In this example, the extracting UDF retrieves the element <customer> from the column ORDER as a VARCHAR data type. The location path is /Order/Customer/Name.


    (I have used this succesfully with my db2 7.2 udb Linux)


    Hope this help you


    Originally posted by thava
    I have done it but not for any help. It only describe how we can retrive informasjon that stored in XMLFILE data type.
    -r-

  5. #5
    Join Date
    Mar 2003
    Posts
    33
    Sorry, the above example was for scalar-function. Here is one for table-function:

    Table function: extractVarchars(xmlobj,'path')


    Example for varchar -data type:

    "select x.returnedvarchar from xml_table as xtb, table(db2xml.extractvarchars(xtb.xml_column, 'path')) as x where .... "

    like:

    db2 "select x.returnedvarchar from xml_table as xtb, table(db2xml.extractVarchars(xtb.xmlfile_column, '/XDocument/XElement')) as x where.."






    Originally posted by raisa
    The link I send You tells how to retrieve content and values of elements and attributes from xmlobj. The xmlobj can be XMLVARCHAR, XMLCLOB or XMLFILE. So you can use this function with yours xmlclob-type column. The syntax is:

    db2xml.extractretrieved_datatype(xmlobj, path)


    Where:

    retrieved_datatype
    Is the data type that is returned from the extracting function; it can be one of the following types:

    * INTEGER
    * SMALLINT
    * DOUBLE
    * REAL
    * CHAR
    * VARCHAR
    * CLOB
    * DATE
    * TIME
    * TIMESTAMP
    * FILE

    xmlobj
    Is the name of the XML column from which the element or attribute is to be extracted. This column must be defined as one of the following XML user-defined types:

    * XMLVARCHAR
    * XMLCLOB as LOCATOR
    * XMLFILE

    path
    Is the location path of the element or attribute in the XML document (such as /Order/Customer/Name). See Location path for location path syntax.


    EXAMPLE:

    SELECT db2xml.extractVarchar(Order, '/Order/Customer/Name') from sales_order_view

    In this example, the extracting UDF retrieves the element <customer> from the column ORDER as a VARCHAR data type. The location path is /Order/Customer/Name.


    (I have used this succesfully with my db2 7.2 udb Linux)


    Hope this help you
    -r-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •