Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Question Unanswered: XML structure stored in varchar attribute

    For example, table with 2 attribute id - NUMBER and desc - VARCHAR
    Content of 2nd attribute (desc) is this below (XML structure).
    Is it possible to write SQL and get data from desc attribute, but only
    <name> and <price>?

    Is there any SQL syntax to select data from xml structure wich is stored i VARCHAR ?

    Is there any db2 restriction to do this (version?)?

    <breakfast_menu>
    <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
    two of our famous Belgian Waffles with plenty of real maple syrup
    </description>
    <calories>650</calories>
    </food>

    ...........................
    ...........................
    </breakfast_menu>

    thx

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    with t(x) as (values '<breakfast_menu>
    <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
    two of our famous Belgian Waffles with plenty of real maple syrup
    </description>
    <calories>650</calories>
    </food>')
    select substr(
       x,
       locate('<name>',x)+6,
       locate('</name>',x) - locate('<name>',x) - 6
    ) as name from t

  3. #3
    Join Date
    Aug 2010
    Posts
    2
    txh, very interesting piece of code, but my idea was to write sql with xml syntax if is possible, something like this

    <contactinfo>
    <Address>
    <street>234 Rolling Lane</street>
    <city>Rockport</city>
    <state>MA</state>
    <zipcode>01210</zipcode>
    </Address>
    <phone>
    <work>9783412321</work>
    <home>9722342134</home>
    <cell>9782452343</cell>
    <satellite>2023051243</satellite>
    </phone>
    <email>love2fish@finmail.com</email>
    </contactinfo>



    and than write sql in client app(squirrel sql) to fetch email data

    select xmlquery(‘$c/contactinfo/email’
    passing contact as “c”)
    from captain
    where state = ‘MA’

    is something like this possible?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Yes, it's possible, only you will need to pass "xmlparse(document c)" instead of just "c" to xmlquery(), since, as you said, your data are stored as strings, not XML. Keep in mind though that it may not perform very well as it will have to parse the string into XML for each row.
    Code:
    with t(x) as (values '<breakfast_menu>
    <food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
    two of our famous Belgian Waffles with plenty of real maple syrup
    </description>
    <calories>650</calories>
    </food></breakfast_menu>')
    
    select xmlquery(
      '$i/breakfast_menu/food/name/text()' 
      passing xmlparse(document x) as "i"
    ) from t

Posting Permissions

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