附注:微软认证考试70-461范围
- Create Database Objects创建数据库对象 (24%)
- Work with Data数据处理 (27%)
- Modify Data数据修改 (24%)
- Troubleshoot & Optimize故障排解及SQL优化 (25%)
本文是第二节Work with Data 数据处理 。
第五部分:Query and manage XML data. May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export xml; xml indexing. XML数据的查询与管理。可能包含但不仅限于:理解XML类型,架构,交互,局限性和约束性;实现XML架构和XML数据的处理;了解什么时候使用XML比较恰当,包括XML命名空间;导入导出XML;XML索引。
理解XML类型,架构,交互,局限性和约束性
创建 xml
类型列:
CREATE TABLE TestXml(id int primary key, details xml)
创建 xml
类型的变量:
DECLARE @x xml
通过指定 XML 架构集合创建类型化的 xml
变量:
declare @x xml (Sales.StoreSurveySchemaCollection)
将 xml
类型参数传递到存储过程:
CREATE PROCEDURE SampleProc(@XmlDoc xml) AS ...
将默认 XML 提供为 XML 常量:
CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>')
将默认 XML 提供为显式 CAST
为 xml
:
CREATE TABLE T (XmlColumn xml default CAST(N'<element1/><element2/>' AS xml))
SQL Server 还支持 xml 类型列的 NULL 和 NOT NULL 约束:
CREATE TABLE T (XmlColumn xml NOT NULL)
指定约束:
创建 xml 类型的列时,可以定义列级或表级的约束。但在指定约束时,不能使用 XML 数据类型方法。另一种方法是创建用户定义函数作为包装来包含 xml 数据类型方法,并在检查约束中指定用户定义函数,如:
CREATE FUNCTION my_udf(@var xml) returns bit
AS BEGIN
RETURN @var.exist('/ProductDescription/@ProductID')
END
注意,如果实例中的<ProductDescription>
元素包含
ProductID
属性,则
xml
数据类型的
exist()
方法返回
1
。否则,返回
0
。现在,您就可以创建带有列级约束的表
现在创建带有列级约束的表,
CREATE TABLE T ( Col1 int primary key, Col2 xml check(dbo.my_udf(Col2)=1))
由于存在约束,因此以下插入失败:
INSERT INTO T values(1,'<Product />')
成功的插入如下:
INSERT INTO T values(1,'<ProductDescription ProductID="1" />')
ALTER TABLE 语句支持 xml 数据类型。例如,可以将任意字符串类型列更改为 xml 数据类型。注意,在这些情况下,列中包含的文档必须格式正确。此外,如果将列的类型从字符串更改为类型化的 xml,则列中的文档将根据指定的 XSD 架构进行验证。
CREATE TABLE T (Col1 int primary key, Col2 nvarchar(max))
GO
INSERT INTO T VALUES (1, '<Root><Product ProductID="1"/></Root>')
GO
ALTER TABLE T ALTER COLUMN Col2 xml
GO
可以将 xml
类型列从非类型化的 XML 更改为类型化的 XML,如
ALTER TABLE T ALTER COLUMN Col2 xml (Production.ProductDescriptionSchemaCollection)
存储在列中的所有实例都将根据指定集合中的 XSD 架构来验证和类型化。如果列包含对于指定架构无效的一个或多个 XML 实例,则 ALTER TABLE
语句将失败,并且您无法将非类型化的 XML 列更改为类型化的 XML。
如果表非常庞大,则修改 xml 类型列的开销会很大。这是因为必须检查每个文档格式是否正确,还必须验证每个文档是否为类型化的 XML。
XML 实例可作为计算列的源或计算列的类型出现。例如,在以下 CREATE TABLE
语句中,通过 col1
计算xml
类型列 (col2
):
CREATE TABLE T(col1 varchar(max), col2 AS CAST(col1 AS xml) )
xml
数据类型还可以作为创建计算列的源出现,如以下 CREATE TABLE
语句中所示:
CREATE TABLE T (col1 xml, col2 as cast(col1 as varchar(1000) ))
可以通过从 xml
类型列中提取值来创建计算列,如以下示例所示。由于不能将 xml 数据类型方法直接用于创建计算列,因此,此示例首先定义可从 XML 实例返回值的函数 (my_udf
)。此函数涵盖xml
类型的 value()
方法。然后在 CREATE TABLE
语句中为计算列指定函数名称。
CREATE FUNCTION my_udf(@var xml)
returns int AS
BEGIN
RETURN @var.value('(/ProductDescription/@ProductModelID)[1]' , 'int')
END
GO
CREATE TABLE T (col1 xml, col2 as dbo.my_udf(col1) )
GO
INSERT INTO T values('<ProductDescription ProductModelID="1" />')
GO
SELECT col2, col1 FROM T
参考:http://msdn.microsoft.com/zh-cn/library/ms189887(v=sql.90).aspx
实现XML架构和XML数据的处理
创建修改删除XML架构集合:
CREATE/ALTER XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expressi
DROP XML SCHEMA COLLECTION...
举例:
CREATE XML SCHEMA COLLECTION TestSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?><xsd:schema 此处省略...</xsd:schema>';
创建带架构的XML列:
CREATE TABLE T ( i int primary key, x xml (TestSchemaCollection))
用变量创建XML架构:
DECLARE @MySchemaCollection nvarchar(max)
Set @MySchemaCollection = N'<?xml version="1.0" encoding="UTF-16"?><xsd:schema 此处省略...</xsd:schema>'
CREATE XML SCHEMA COLLECTION MyCollection AS @MySchemaCollection
在创建 XML 架构集合时,可以指定多个 XML 架构。 例如:
CREATE XML SCHEMA COLLECTION MyCollection AS N'
<xsd:schema targetNamespace="namespace1">
<!-- 架构内容 -->
</xsd:schema>
<xsd:schema targetNamespace="namespace2">
<!-- 架构内容 -->
</xsd:schema>';
导入未指定目标命名空间的架构
如果向集合中导入未包含 targetNamespace 属性的架构,该架构的组件将与空字符串目标命名空间相关联,如下面的示例所示。注意,如果不关联在集合中导入的一个或多个架构,将导致多个架构组件(可能是无关的)与默认空字符串命名空间关联。
-- 创建没有命名空间的架构CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:ns="http://ns">
<element name="e" type="dateTime"/>
</schema>'
go
-- 此查询返回所有没有命名空间的架构
SELECT sys.xml_schema_collections.name
FROM sys.xml_schema_collections
JOIN sys.xml_schema_namespaces
ON sys.xml_schema_collections.xml_collection_id =
sys.xml_schema_namespaces.xml_collection_id
WHERE sys.xml_schema_namespaces.name=''
在同一批处理中,不能既创建XML架构同时又使用此架构创建XML列,如:
CREATE XML SCHEMA COLLECTION mySC AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<element name="root" type="string"/>
</schema>
'
GO
CREATE TABLE T (Col1 xml (mySC))
GO
此批处理执行没有问题,但是如果把红色的GO去掉,就会报错。
参考:http://msdn.microsoft.com/zh-cn/library/ms176009(v=sql.110).aspx