July 22, 2005 at 9:16 am
Hey,
I posted this over here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=203328
but I want to do it using T-SQL, so it seems relevant here as well.
have a some data stored in XML in a field in a table. I didn't make, it, but I get to report off of it [Sad]
So, I'm hoping that someone here has some brilliant ideas on getting data out of an xml tree in a field. Something like:
select t.field.xml_path
from t
Thanks kindly.
July 22, 2005 at 9:23 am
They have xpath queries in sql 2005.
But the decision to store an entire string of xml in a column in a table, and expect to use it for reporting an analysis was a critical mistake.
You will be much better served to create proper normalized tables, and insert the data from the xml strings into the tables.
Even when sql 2005 comes out just because there are xpath queries does not mean it will be a good idea to implement something like this.
Maybe someone does have something brilliant. But Its not likely to perform well.
July 22, 2005 at 9:28 am
I couldn't agree more, Ray, but I'm not on the data collecting side of the application so I don't have much choice.
I looked in the books online for 2000 (what we're using) and they show some Xpath query information, but it's not clear to me how to implement this in a SQL query. Perhaps that's because it wasn't meant to do this...
Thanks again.
Greg
July 22, 2005 at 9:31 am
You of course could evaluate every row, one row at a time, but I do not know if a set based way to do it.
In books online you will have to use an Openxml construct. Within a cursor or loop. But if your where clause includes values within the xml, I'm not sure if there is an efficient way to do it other than
Where xmlstringcol like '<MyElement>Myvalue</MyElement>'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply