Post reply

Help with parsing XML using OPENXML in sql server 2005

  • January 14, 2009 at 2:55 pm

    #77175

    Hi,

    I am trying to parse this XML in SQL 2005. I have tried several different ways and have had no luck. My latest attempt was with OPENXML. I don't get any error message but the data is not being returned.

    I would be greatful for any help with this. Parsing XML is not my strong suit.

    The code is attached in a separate file since it contains XML.

    Thanks!

    Maureen

  • January 14, 2009 at 3:27 pm

    #926986

    I just tried using your XML with the value XML method, and it seems to work as documented. I don't have your schema, so I had to simplify a couple bits, but it did work. Have you tried using that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 14, 2009 at 3:39 pm

    #926993

    Could you post your syntax?

    I tried:

    set @presp = @pFile.value('(/*:Enroll/../Context/@SubmitterUserId)[1]', 'varchar(30)')

    and get a null result.

    Thanks!!!

  • January 15, 2009 at 7:20 am

    #927281

    declare @pFile XML

    set @pFile = '(Your XML string)'

    select @pFile.value('(*:Enroll/Context/@SubmitterUserId)[1]','varchar(100)')

    Result:

    THEUSER

    (Can't post the XML here, but it's a copy-and-paste from your file.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 15, 2009 at 3:45 pm

    #927773

    Thanks very much! It works great.

Viewing 5 posts - 1 through 5 (of 5 total)

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