Extracting XML File that is stored as a text data type

Question

Extracting XML File that is stored as a text data type

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

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

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 1

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"

Kafkaesque SSC-Addicted Points: 405 More actions · Answer 2

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.

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 3

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"

ahmer.sajjad Grasshopper Points: 21 More actions · Answer 4

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

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]

ahmer.sajjad Grasshopper Points: 21 More actions · Answer 6

Please find the xml file attachmed. Cheers

want to return NxxxxqM from node CompanyCode

currently I am doing this using charindex and substring.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]

ahmer.sajjad Grasshopper Points: 21 More actions · Answer 8

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

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]

Kafkaesque SSC-Addicted Points: 405 More actions · Answer 10

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