March 26, 2009 at 8:07 am
I have a column in a table that stores xml files but with the text datatype. I need to drill down and extract the contents of a specific node. I started having a look at xquery but as the data is stored as a text data type and not xml it doesn't work. I looked on msn site and found that you cannot convert or cast from text to xml. I am using sql server 2005.
Anyone got any ideas? Would like to get this one licked.
March 26, 2009 at 8:13 am
However, you can convert from Text to Varchar(MAX) and from Varchar to XML.
[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]
March 26, 2009 at 8:41 am
Here's what i have for double casting
SELECT CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML)
March 26, 2009 at 8:43 am
Getting this error
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "XMLMessage" or the user-defined function or aggregate "XMLMessage.query", or the name is ambiguous.
March 26, 2009 at 2:28 pm
I think that you will have to give us the Table definition (CREATE TABLE, please) and tell us what you are trying to do with this query because it is not clear either from the query or from you previous post (they appear contradictory). If you give us the table definition and tell us what output or result you are trying to get I am sure that we can get you there.
[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]
March 26, 2009 at 4:43 pm
I don't have access right now. I have a column called XMLMessage which stores xml data etc, typical xml but the data type that defines the column is text that's why i'm casting to change the datatype to xml so i can xquery it. Becaus eof the nature of the data (sensitive) I can't post it.
table is called
DocSource
column
XMLMessage datatype text
contents of that column may look like this
don't know if this is any clearer. I'll try to get a better example.
March 26, 2009 at 5:33 pm
Sample File should be here
March 26, 2009 at 7:41 pm
Then this should work:
SELECT CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).query('/PatientId/IdValue')
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]
March 27, 2009 at 4:01 am
CHeers,
This looks promising but is not return the contents of the node. The return columns are blank although data is in the table that it is coming from.
cheers again, this is a step in the right direction.
March 27, 2009 at 5:23 am
That's a problem with the xQuery then. I cannot help you with that unless you are willing to post the XML or a least a content-sanitized version (tags & attribute names must still be the same though).
[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]
March 27, 2009 at 6:23 am
here's a sanitised version
March 27, 2009 at 9:03 am
Both of these should work then:
SELECT CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).value('(/CData/DocumentOrganisationName)[1]', 'NVarchar(50)')
, CAST(CAST(XMLMessage as VARCHAR(MAX)) AS XML).query('(/CData/DocumentOrganisationName/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]
March 27, 2009 at 9:38 am
I sent you a simplified version of what was needed. i've reworked what you sent and i am now getting nulls in the columns returned. it's still a breakthrough. COuld that be the result of not navigating the xml document properly. It is quite a large docuemtn and i haven't had much experience of xml xquery etc.
If it's easier you can email me at emkafkaesque@googlemail.com
March 27, 2009 at 9:45 am
Nulls probably indicate that you are misspelling the tag names. Remember that XML is case-sensitive.
[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]
March 27, 2009 at 10:17 am
Two other things to think about:
- CDATA is actually a reserved keyword in XML. You apparently have it capitalized a little different so it may not be interfering, but that was a really unfortunate choice for the tag name. The reason I bring this up is CDATA is used as a marker around text blocks in order to tell XML to IGNORE markup within those blocks....
- If your XML blocks have namespace definitions (which you may have remove to "simplify" things), the xquery will need to be modified. In other words - a perfectly valid XQuery that runs just fine against something with NO namespace may fail or return nothing in the presence of a namespace declaration.
----------------------------------------------------------------------------------
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 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply