Fetching value from XML column

  • I have a column that contains some XML data. The column is defined as:

    [Reply] [xml] NULL,

    Now, I'm sure I pulled data out of this column with statements like:

    SELECT Reply.Value('AUTH_CODE')

    But now it says that Value is not a valid function, property or field. I thought it was cool I could extract info this way.

    I have other columns in the table for the WHERE clause so I don't have to look in all the rows to find the rows I want.

    What have I overlooked? The help isn't being a lot of help.:hehe:

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Your xml did not post right. Before posting change all your less than and greater than to the appropriate HTML.

    e.g.

    & lt; and & gt;

  • You can also post your xml document as a text attachement.

  • I didn't post any XML, just the column definition from the CREATE TABLE statement:P

    <XML_REQUEST>

    <USER_ID>Term0090</USER_ID>

    <TROUTD>12868</TROUTD>

    <RESULT>CAPTURED</RESULT>

    <AUTH_CODE>DEMO-3</AUTH_CODE>

    <INTRN_SEQ_NUM>12868</INTRN_SEQ_NUM>

    </XML_REQUEST>

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Your right :w00t:

    For some reason it looked stripped to me before, but now that you have clarified it makes sense.

    You can defintely use the value method to retreive the xml value.

    Try

    SELECT Reply.value('(/XML_REQUEST/AUTH_CODE)[1]', 'VARCHAR(20)')

    from MyTable

    I cant test the query right now, but it should work.

  • That works! I knew I'd done that, but if I hadn't solved the problem in another 10 minutes, I'd have written my own UDF;)

    [font="Arial"]Halfbubble ------------
    You just can't do this stuff unless you're at least half a bubble off center.[/font]

  • Keep in mind that this will always retrieve just one value per row (so - you're out of luck with this syntax if the row has multiple instances of that node).

    If your data needs to pull out multiple rows out of the XML column - you will need to look up the xml.NODES() syntax and use that.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply