March 9, 2009 at 4:50 pm
Hey all, I need some help querying the value from the tag. For instance, in this example I would need a query that can return "33" and then will need to write that value to two other tables (Customers and weblead). Ive looked into a few possibilities using OPENXML and the nodes method, but I feel like I am way off here. The xml has been written to a column called leadadf and the data type is text (this db existed prior to typed xml). Thanks in advance to anybody that can get me on the right path! I have also included a screenshoot from IE of the raw XML.
March 9, 2009 at 5:05 pm
Now that Im thinking about it, is it even possible to query xml from a column with a data type other than xml? 😀
March 9, 2009 at 6:19 pm
Yes, the text datatype can be converted to the xml data type in SQL Server 2005.
You could potentially use either the pre-2005 OPENXML method (together with sp_xml_preparedocument), or use XQuery on the text converted to the xml datatype.
March 9, 2009 at 9:23 pm
I think that you will find the XML.value method most useful. Hardly anyone uses OPENXML anymore now that the XML datatype and methods are available.
If you could post an attachment form that would allow us to cut and paste the XML we could probably come up with the proper Xquery. Off hand, I would try something like @xml.value('./vendor[1]/agentNo[1]', 'int')
[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]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply