I was trying to understand the basic advantage of using XML DataType in SQL Server 2005. I underwent the article here, saying that in case you want to delete multiple records. Serialize the XMl, send it in Database and using below query you can delete it..
我试图了解在SQL Server 2005中使用XML DataType的基本优势。我在这里接受了这篇文章，说如果你想删除多个记录。序列化XMl，在数据库中发送它并使用下面的查询你可以删除它..
I was curious to look into any other advantage of using this DataType...
我很想知道使用这个DataType的任何其他优点......
EDIT
编辑
Reasons for Storing XML Data in SQL Server 2005
在SQL Server 2005中存储XML数据的原因
Here are some reasons for using native XML features in SQL Server 2005 as opposed to managing your XML data in the file system:
以下是在SQL Server 2005中使用本机XML功能而不是在文件系统中管理XML数据的一些原因：
-
You want to use administrative functionality of the database server for managing your XML data (for example, backup, recovery and replication).
您希望使用数据库服务器的管理功能来管理XML数据（例如，备份，恢复和复制）。
My Understanding - Can you share some knowledge over it to make it clear?
我的理解 - 你能否分享一些知识以表明它？
-
You want to share, query, and modify your XML data in an efficient and transacted way. Fine-grained data access is important to your application. For example, you may want to insert a new section without replacing your whole document.
您希望以高效和事务的方式共享，查询和修改XML数据。细粒度数据访问对您的应用程序很重要。例如，您可能希望插入新的部分而不替换整个文档。
My Understanding - XML is in specific column row, In order to add new section in this row's cell, Update is required, so whole document will be updated. Right?
我的理解 - XML在特定列行中，为了在此行的单元格中添加新部分，需要更新，因此将更新整个文档。对？
-
You want the server to guarantee well-formed data, and optionally validate your data according to XML schemas.
您希望服务器保证格式良好的数据，并根据XML模式选择性地验证数据。
My Understanding - Can you share some knowledge over it to make it clear?
我的理解 - 你能否分享一些知识以表明它？
-
You want indexing of XML data for efficient query processing and good scalability, and the use a first-rate query optimizer.
您希望索引XML数据以实现高效的查询处理和良好的可伸缩性，并使用一流的查询优化器。
My Understanding - Same can be done by adding individual columns. Then why XML column?
我的理解 - 可以通过添加单个列来完成相同的操作。那么为什么XML列呢？
2 个解决方案
#1
1
Pros: Allows storage of xml data that can be automatically controlled by an xml schema - thereby guaranteeing a certain level of data quality Many web/desktop apps store data in xml form, these can then be easily stored and queried in the database - so it is a great place to store xml data that an app may need to use (e.g. for configuration settings)
优点：允许存储可由xml架构自动控制的xml数据 - 从而保证一定程度的数据质量许多Web /桌面应用程序以xml格式存储数据，然后可以在数据库中轻松存储和查询这些数据 - 因此它是一个存储应用程序可能需要使用的xml数据的好地方（例如，用于配置设置）
Cons: Be careful about using xml fields, they may start off as innocent storage but can become a performance nightmare if you want to search, analyse and report on many records.
缺点：小心使用xml字段，它们可能从无辜存储开始，但如果要搜索，分析和报告许多记录，可能会成为性能噩梦。
Also, if xml fields will be added to, changed or deleted this can be slow and leads to complex t-sql.
此外，如果将添加，更改或删除xml字段，这可能会很慢并导致复杂的t-sql。
In replication, the whole xml gets updated even if only one node changes - therefore you could have many more conflicts that cannot easily be resolved.
在复制中，即使只有一个节点发生更改，整个xml也会更新 - 因此您可能会遇到更多无法轻松解决的冲突。
#2
0
I would say of the 4 advantages you've listed, these two are critical:
我会说你列出的4个优点，这两个是至关重要的：
You want to share, query, and modify your XML data in an efficient and transacted way
您希望以高效和事务的方式共享，查询和修改XML数据
SQL Server stores the XML in an optimised way that it wouldn't for plain strings, and lets you query the XML in an efficient way, rather than requiring you to bring the entire XML document back to the client. Think how inefficient it is if you want to query 10,000 XML columns, each containing 1k of data. For a small XPath query you would need to return 10k of data across the wire, for each client, each time.
SQL Server以优化的方式存储XML，而不是纯字符串，并且允许您以有效的方式查询XML，而不是要求您将整个XML文档带回客户端。想想如果要查询10,000个XML列，每个列包含1k个数据，效率会有多低。对于小型XPath查询，您需要每次为每个客户端通过线路返回10k数据。
You want indexing of XML data for efficient query processing and good scalability, and the use a first-rate query optimizer
您希望索引XML数据以实现高效的查询处理和良好的可伸缩性，并使用一流的查询优化器
This ties into what I said above, it's far more efficiently stored than a plain text column which would also run into page fragmentation issues.
这与我上面所说的相关，它比纯文本列更有效地存储，纯文本列也会遇到页面碎片问题。
#1
1
Pros: Allows storage of xml data that can be automatically controlled by an xml schema - thereby guaranteeing a certain level of data quality Many web/desktop apps store data in xml form, these can then be easily stored and queried in the database - so it is a great place to store xml data that an app may need to use (e.g. for configuration settings)
优点：允许存储可由xml架构自动控制的xml数据 - 从而保证一定程度的数据质量许多Web /桌面应用程序以xml格式存储数据，然后可以在数据库中轻松存储和查询这些数据 - 因此它是一个存储应用程序可能需要使用的xml数据的好地方（例如，用于配置设置）
Cons: Be careful about using xml fields, they may start off as innocent storage but can become a performance nightmare if you want to search, analyse and report on many records.
缺点：小心使用xml字段，它们可能从无辜存储开始，但如果要搜索，分析和报告许多记录，可能会成为性能噩梦。
Also, if xml fields will be added to, changed or deleted this can be slow and leads to complex t-sql.
此外，如果将添加，更改或删除xml字段，这可能会很慢并导致复杂的t-sql。
In replication, the whole xml gets updated even if only one node changes - therefore you could have many more conflicts that cannot easily be resolved.
在复制中，即使只有一个节点发生更改，整个xml也会更新 - 因此您可能会遇到更多无法轻松解决的冲突。
#2
0
I would say of the 4 advantages you've listed, these two are critical:
我会说你列出的4个优点，这两个是至关重要的：
You want to share, query, and modify your XML data in an efficient and transacted way
您希望以高效和事务的方式共享，查询和修改XML数据
SQL Server stores the XML in an optimised way that it wouldn't for plain strings, and lets you query the XML in an efficient way, rather than requiring you to bring the entire XML document back to the client. Think how inefficient it is if you want to query 10,000 XML columns, each containing 1k of data. For a small XPath query you would need to return 10k of data across the wire, for each client, each time.
SQL Server以优化的方式存储XML，而不是纯字符串，并且允许您以有效的方式查询XML，而不是要求您将整个XML文档带回客户端。想想如果要查询10,000个XML列，每个列包含1k个数据，效率会有多低。对于小型XPath查询，您需要每次为每个客户端通过线路返回10k数据。
You want indexing of XML data for efficient query processing and good scalability, and the use a first-rate query optimizer
您希望索引XML数据以实现高效的查询处理和良好的可伸缩性，并使用一流的查询优化器
This ties into what I said above, it's far more efficiently stored than a plain text column which would also run into page fragmentation issues.
这与我上面所说的相关，它比纯文本列更有效地存储，纯文本列也会遇到页面碎片问题。