#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Handling NULL value in XML using getStringVal


    Hi,
    I have below query which works fine if column 'XML_COL' has values. This select statement fails if the value is NULL for

    select xmltype(t.xml_col).extract('//fax/text()').getStringVal() from mytab t

    How to handle rows with NULL values in the column 'XML_COL'.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by ushelke
    How to handle rows with NULL values in the column 'XML_COL'.
    Why not add a
    Code:
    WHERE xml_col IS NOT NULL
    to your statement?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by shammat
    Why not add a
    Code:
    WHERE xml_col IS NOT NULL
    to your statement?
    Unfortunately I need all the values as this SELECT is part of another SELECT
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by ushelke
    Unfortunately I need all the values as this SELECT is part of another SELECT
    added the CASE statement in SELECT statemnet to first check if the value is NULL or NOT. This is working fine.
    Thanks

    select
    CASE WHEN
    t.xml_col IS NULL THEN NULL
    ELSE
    xmltype(t.xml_col).extract('//fax/text()').getStringVal()
    END
    from mytab t

IMN logo majestic logo threadwatch logo seochat tools logo