Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: XML Document - Retrieving

    DB2 v 7.2 FP 9 and XML Extender on AIX ..

    My XML Document is in /tmp/xmldoc :

    <products>
    <product code="PROD1" type="type1">
    <description>Product1</description>
    <usage>usage1</usage>
    </product>

    <product code="PROD2" type="type2">
    <description>Product2</description>
    <usage>usage2</usage>
    </product>
    </products>

    When I issue ,

    select * from table(db2xml.extractvarchars(db2xml.xmlfile('/tmp/xmldoc'),'/products/product/@code')) as z"

    RETURNEDVARCHAR
    --------------------------
    PROD1
    PROD2

    How do I get a four column table output as below :


    PROD1 type1 Prodcut1 usage1
    PROD2 type2 Prodcut2 usage2

    Thanks for your replies ...

    Cheers
    Sathyaram

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: XML Document - Retrieving

    Originally posted by sathyaram_s
    DB2 v 7.2 FP 9 and XML Extender on AIX ..

    My XML Document is in /tmp/xmldoc :

    <products>
    <product code="PROD1" type="type1">
    <description>Product1</description>
    <usage>usage1</usage>
    </product>

    <product code="PROD2" type="type2">
    <description>Product2</description>
    <usage>usage2</usage>
    </product>
    </products>

    When I issue ,

    select * from table(db2xml.extractvarchars(db2xml.xmlfile('/tmp/xmldoc'),'/products/product/@code')) as z"

    RETURNEDVARCHAR
    --------------------------
    PROD1
    PROD2

    How do I get a four column table output as below :


    PROD1 type1 Prodcut1 usage1
    PROD2 type2 Prodcut2 usage2

    Thanks for your replies ...

    Cheers
    Sathyaram
    I don't have XML Extender installed so I can't check right away but I don't believe there's a straightforward way to accomplish this.

    You could rely on the XML extender processing your xml file in the same order every time it accesses it, and do the following:
    - declare four tables similar to
    create table t_code (
    id integer generated always as identity,
    value varchar(128)
    );

    - insert each element (attribute) into its own table:
    insert into t_code (value) select returnedvarchar from table(db2xml.extractvarchars(db2xml.xmlfile('/tmp/xmldoc'),'/products/product/@code')) as z;

    - then select values from those 4 tables joining them by id

    I hope the above makes sense...

    On the other hand, if this is something that you'll be doing frequently a better solution would be to create a table with an xml column and side tables for attribute values.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: XML Document - Retrieving

    Thank you nick for the reply ...

    I'll try this one tomorrow or else I'll have to awk/sed it ...

    Cheers

    sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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