SQL Server Performance Forum – Threads Archive

SQL Server 2005 XML Question

Hi Friends, I need ur help in one of issue. Please suggest… Does SQL Server 2005 support an XML datatype? If so, can you explain to me how an XML object is stored physically? Can it be isolated from the rest of the attributes that might be associated to it and only fetched in when an SQL requests that XML object? The issue here is that the development team (and us) are designing a method to log messages that are coming to BIS. They want to log each message but they want to break the XML part out into its own table even though it would have a one-to-one relationship with its sibling table. Ideally, it would be better to have just one table, but physically find away to place the large, XML, attribute into its own storage area (and not with the rest of the row’s data). Thanks a million for ur help in advance. Mast MAST
ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVES UP.

---

SQL Server 2005 supports xml datatype so you can basically have a table with xml columns. Check these sites for further info:
http://builder.com.com/5100-6388_14-6140404.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql25xmlbp.asp Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Hi Friends, I have a doubt here. If I define a table as follows: Create table xxxx.table_name
(Unique_keyinteger,
Column1date,
Column2char(10),
Column3XML
) If I query the table with the following query: Select unique_key, column1, column2
from xxxx.table_name; Will I incur the I/O of bringing in Column3, even though I did not request it? Or can an XML data type attribute be isolated physically from the rest of the row and not be brougt in unless specifically requested? I want to understand the storing structure of SQL Server 2005 for XML data type. It will store the physical text somewhere else & put pointer in the mentioned column like image datatype or it will store physcial code in the column itself. Please help me urgently needed..
Thanks
MAST
ITS IMPOSSIBLE TO DEFEAT A PERSON WHO NEVER GIVES UP.

---

I believe you haven’t referred to Dilligrg’s link
http://www.developer.com/db/article.php/3531196
http://www.developer.com/db/article.php/3565996
with xml indexing you need not worry about performance. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>