Post reply

Extracting XML File that is stored as a text data type

  • April 6, 2009 at 4:17 am

    #972397

    Hi,

    I have quick look over all the msgs in the post. My confusion is, why dont use XML datatype for the column instead of 'text'?, as the database is already SQL Server 2005!! Sorry, if i am mistaken.

    "Don't limit your challenges, challenge your limits"

  • April 6, 2009 at 3:03 pm

    #972925

    Hi,

    I agree but when i mentioned this i was told it was from an older system. xml data type would have made more sense.

  • April 6, 2009 at 10:26 pm

    #973074

    yeah!!!

    then, i think, to fight all the time with text type xml can b replaced with one time struggle in converting text to xml............

    what u say?

    "Don't limit your challenges, challenge your limits"

  • April 7, 2009 at 6:42 pm

    #973940

    Hi Barry,

    I am trying to query SQL Server 2005 table column which is text type and contains XML Message

    The following is an example of the XML message:

    I need to retrieve CompanyCode from the above example. Please help.

  • April 7, 2009 at 7:12 pm

    #973946

    ahmer:

    Attach it as a txt file.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 7, 2009 at 9:49 pm

    #973985

    Please find the xml file attachmed. Cheers

    want to return NxxxxqM from node CompanyCode

    currently I am doing this using charindex and substring.

  • April 7, 2009 at 10:30 pm

    #973999

    This isn't valid XML. For one thing, the "NS8" prefix is never declared.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 7, 2009 at 10:39 pm

    #974003

    If the xml file was valid, how would you search for CompanyCode within the XML which is a text column? just want to know the systax for XML).query(???? please

  • April 7, 2009 at 11:01 pm

    #974014

    Probably either of these:

    SELECT CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).value('(//*:CompanyCode)[1]', 'NVarchar(50)')

    , CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).query('(//*:CompanyCode/text())')

    from DocumentSource

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 8, 2009 at 4:10 am

    #974123

    Yeah thats why i did the casting, it helped. but maybe would be ebtter to use proper data types in future.

Viewing 10 posts - 31 through 39 (of 39 total)

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