Retrieve data from column with xml data type, SQL Server 208

[A Petkovic]

Situation: One of the columns in the table is with xml data type. Every seconds table has ~30 row inserted. After couple of mounts there will be ~ 30row x 60s x 60min x 24h x 30day = ~78x10^6 rows for 1 month. The goal is: very quick retrieve data from that table, where some queries will target xml column (specific data in xml element or attribute). Did anybody have experience with xml column in this way? Did this way of returning data is slow, or can be quick with using xml indexes, or even using xml indexes this way of returning data is still slow?

___________________________ A.Petkovic B.Sc.Eng., MCSD, MCT

[Vimalsoft Pty Ltd]

if i understand you correctly , you have a field in your table that is of XML data type and has some xml in it and you want to retrieve a certain attribute(Column) in that XML. Then my frinend , that means form the begining you made a poor design decision when designing a database, and to loop through the data will be tedious for sql to retrieve the amaount of data you want. use another way.Give us more info , mybe we can help in the Logic that you can follow.

Vuyiswa Maseko, Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers." C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.somee.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[A Petkovic]

I will try to explain, in one db I have 3 tables, because I want to return data as quick as possible to web services, I design 1 table with all necessary columns. First design is without xml column, but number of rows will be at least 5x larger from design with xml column. One of the solution is that I return data, and in web services to deal with xml column. Second solution is that I return data filtered by xml attributes, or elements. One more quick response is goal.

___________________________ A.Petkovic B.Sc.Eng., MCSD, MCT