Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Question Unanswered: Parsing XML from VARCHAR Column

    I am working with db2 9.0
    I have one column(VARCHAR(200)) which contains value in xml :
    <?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><name language-id="en_US">Home</name></root>

    I wanted to parse the name from the above column value through query.

    Can any one please help me how can I do this?

    Thanks,
    Hemen

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    can you be more specific ?
    do you want to use xquery on this col ?
    or have a look at http://publib.boulder.ibm.com/infoce.../c0023533.html
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Hemen Punjani View Post
    I am working with db2 9.0
    I have one column(VARCHAR(200)) which contains value in xml :
    <?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><name language-id="en_US">Home</name></root>

    I wanted to parse the name from the above column value through query.

    Can any one please help me how can I do this?

    Thanks,
    Hemen
    Something like:

    Code:
    with T(s) as (
      values
        XMLPARSE( DOCUMENT
            '<?xml version="1.0" encoding="UTF-8"?>
                <root available-locales="en_US" default-locale="en_US">
                    <name language-id="en_US">Home</name>
                </root>'
        PRESERVE WHITESPACE )
    )
    select XMLQUERY ( '$s/*:root/*:name' passing s as "s" )
    from T;
    Personally I find xml rather wierd, and I never really got used to it
    --
    Lennart

  4. #4
    Join Date
    Sep 2012
    Posts
    5
    Quote Originally Posted by przytula_guy View Post
    can you be more specific ?
    do you want to use xquery on this col ?
    or have a look at XML parsing
    Hi przytula_guy,

    Thanks.

    Details are as follow :
    I have one table test.
    It has 2 column .
    1 - id - Integer
    2 - name -VARCHAR(200).

    the data in the table is as follow :
    id = 1
    name = <?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><name language-id="en_US">Home</name></root>

    I want extract "Home" from the above record.

    Hope detail give you the idea.

    Thanks,
    Hemen

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    I have also tried with :
    extractVarchar, xmlQuery and XMLCAST functions.

    They are not working because here datatype of the Name is VARCHAR.

    Select (xmlquery('$c/root/name' passing XMLCAST(TEST.NAME AS XML) as 'c')) from TEST
    Select extractVarchar(TEST.NAME ,'/root/name') from TEST

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Hemen Punjani View Post
    I have also tried with :
    extractVarchar, xmlQuery and XMLCAST functions.

    They are not working because here datatype of the Name is VARCHAR.

    Select (xmlquery('$c/root/name' passing XMLCAST(TEST.NAME AS XML) as 'c')) from TEST
    Select extractVarchar(TEST.NAME ,'/root/name') from TEST
    You need to create an XML type from your VARCHAR column. One example of how to do that:

    Code:
    with T(id, name) as (
      values (1, '<?xml version="1.0" encoding="UTF-8"?>
                <root available-locales="en_US" default-locale="en_US">
                    <name language-id="en_US">Home</name>
                </root>')
    ), X(id, s) as (
        select id,
            XMLPARSE( DOCUMENT name PRESERVE WHITESPACE)
        from T
    )
    select XMLQUERY ( '$s/*:root/*:name' passing s as "s" )
    from X;
    --
    Lennart

  7. #7
    Join Date
    Sep 2012
    Posts
    5
    Yes Lennart - you are right, if the datatype is XML then it would be done.

    But I cant do the change in datatype.

    Is there any other way, to extract name field by query?

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Hemen Punjani View Post
    Yes Lennart - you are right, if the datatype is XML then it would be done.

    But I cant do the change in datatype.

    Is there any other way, to extract name field by query?

    You missunderstould me. Assuming

    Code:
    create table test ( id int not null primary key, name varcharchar(200) not null);
    insert into test (id, name)
    values (1, '<?xml version="1.0" encoding="UTF-8"?>
                    <root available-locales="en_US" default-locale="en_US">
                        <name language-id="en_US">Home</name>
                    </root>');
    You can create the xml on the fly as in:

    Code:
    select XMLQUERY ( '$s/*:root/*:name' passing xmlname as "s" )
    from (
        select XMLPARSE( DOCUMENT name PRESERVE WHITESPACE) as xmlname from test
    ) as T;
    --
    Lennart

  9. #9
    Join Date
    Sep 2012
    Posts
    5

    Thumbs up

    Quote Originally Posted by lelle12 View Post
    You missunderstould me. Assuming

    Code:
    create table test ( id int not null primary key, name varcharchar(200) not null);
    insert into test (id, name)
    values (1, '<?xml version="1.0" encoding="UTF-8"?>
                    <root available-locales="en_US" default-locale="en_US">
                        <name language-id="en_US">Home</name>
                    </root>');
    You can create the xml on the fly as in:

    Code:
    select XMLQUERY ( '$s/*:root/*:name' passing xmlname as "s" )
    from (
        select XMLPARSE( DOCUMENT name PRESERVE WHITESPACE) as xmlname from test
    ) as T;
    Yeah. I didnt read carefully, Sorry!!
    It works.
    Great!! Thanks!

Tags for this Thread

Posting Permissions

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