关于sqlserver中xml的一些范例

主要是用来给自己备忘用的，至少可以知道对一些问题应该去查联机丛书的什么部分。


--申明xml变量
Declare an XML variable
DECLARE @x XML
--Declare a TYPED XML Variable
DECLARE @x XML(CustomerSchema)
---- Declare a TYPED XML DOCUMENT Variable
DECLARE @x XML(DOCUMENT CustomerSchema)
---- Declare a TYPED XML CONTENT variable
DECLARE @x XML(CONTENT CustomerSchema)

--把一个返回xml的查询结果赋给xml变量
DECLARE @x XML
SELECT @x = (
SELECT OrderID
FROM OrderHeader
FOR XML AUTO, TYPE)

--从磁盘打开一个xml文档，并将其赋与一个xml变量
/*bol如是说：SINGLE_BLOB
将 data_file 的内容作为类型为 varbinary(max) 的单行单列行集返回。

重要提示：
我们建议您仅使用 SINGLE_BLOB 选项（而不是 SINGLE_CLOB 和 SINGLE_NCLOB）
导入 XML 数据，因为只有 SINGLE_BLOB 支持所有的 Windows 编码转换
To use the OPENROWSET(BULK..) option, the user should have
ADMINISTRATOR BULK OPERATIONS permission.
*/
DECLARE @xml XML
SELECT
@xml = CAST(bulkcolumn AS XML)
FROM OPENROWSET(BULK 'C:/temp/items.xml', SINGLE_BLOB) AS x


/*一个查询xml节点属性的例子*/
SELECT
x.value('@ItemNumber','CHAR(4)') AS ItemNumber,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM (
SELECT CAST(bulkcolumn AS XML) AS data
FROM OPENROWSET(BULK 'C:/temp/items.xml', SINGLE_BLOB)
AS x
) a
CROSS APPLY data.nodes('/Items/Item') i(x)--i(x)i是表名，x是i表的列名，也可以写成as i(x)


--xml数据类型的方法示例
value('xpath str','casted as datatype')
DECLARE @x XML
SELECt @x = '<Order OrderID="1" OrderNumber="SO101" />'
SELECT
@x.value('(Order/@OrderID)[1]','INT') AS OrderID,
@x.value('(Order/@OrderNumber)[1]','CHAR(5)') AS OrderNumber

---nodes('xpath')
DECLARE @x XML
SELECT @x = '<Items>
<ItemNumber>1001</ItemNumber>
<ItemNumber>1002</ItemNumber>
</Items>'
SELECT
x.value('.','CHAR(4)') AS ItemNumber
FROM @x.nodes('/Items/ItemNumber') o(x)

--node()与cross apply，itemdata为orderxml表中的列
SELECT
OrderID,
x.value('@ItemNumber','CHAR(4)') AS ItemNumber
FROM OrderXML
CROSS APPLY ItemData.nodes('/Order/Item') o(x)

---exist('xpath')
SELECT
OrderID
FROM OrderXML
WHERE ItemData.exist('/Order/Item[@ItemNumber = "Z001"]') = 1


--Joining XML nodes with relational tables
SELECT
oh.OrderID,
c.Name AS Customer,
i.ItemDescription AS Item,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM OrderHeader oh
INNER JOIN OrderXML ox ON
ItemData.value('(Order/@OrderID)[1]','INT') = oh.OrderID
CROSS APPLY ItemData.nodes('/Order/Item') o(x)


/*Another way to write the preceding query is to embed the join operators
as part of the XQuery expression itself.*/
SELECT
oh.OrderID,
c.Name AS Customer,
i.ItemDescription AS Item,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM OrderHeader oh
INNER JOIN Customers c ON c.CustomerID = oh.CustomerID
CROSS JOIN OrderXML
CROSS JOIN Items i
CROSS APPLY ItemData.nodes('
/Order[@OrderID=sql:column("oh.OrderID")]
/Item[@ItemNumber=sql:column("i.ItemNumber")]') o(x)


/*SQL Server allows only string literals as XQuery expressions. The following is illegal in SQL
Server 2008*/
DECLARE @node VARCHAR(100)
SELECT @node = '/Order/Item'
SELECT
/* columns here */
FROM OrderXML
CROSS APPLY ItemData.nodes(@node) o(x)

/*sqlserver不支持在xquery中直接使用变量或者将变量直接设置成xpath,但在xquery中可以使用变量设置属性和元素名等*/
/*sql:variable()*/
DECLARE @ItemNumber CHAR(4)
SELECT @ItemNumber = 'D001'
SELECT
x.value('@ItemNumber','CHAR(4)') AS ItemNumber,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM OrderXML
CROSS APPLY ItemData.nodes('
/Order/Item[@ItemNumber=sql:variable("@ItemNumber")]'
) o(x)

/*XQuery function local-name()来解决变量设置xml元素名或属性名的问题*/
DECLARE @Att VARCHAR(50)
SELECT @Att = 'ItemNumber'
SELECT
x.value('@*[local-name()=sql:variable("@Att")][1]',
'VARCHAR(50)') AS Value
FROM OrderXML
CROSS APPLY ItemData.nodes('/Order/Item') o(x)

/*Accessing the parent node，父节点访问符../只适用于小表或小的xml文档，对于大的，还是使用下面的cross applay
更好*/
SELECT
x.value('../@OrderID','INT') AS OrderID,
x.value('@ItemNumber','CHAR(4)') AS ItemNumber,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM OrderXML
CROSS APPLY ItemData.nodes('/Order/Item') o(x)

/*以上查询的优化写法为*/
SELECT
h.value('@OrderID','INT') AS OrderID,
x.value('@ItemNumber','CHAR(4)') AS ItemNumber,
x.value('@Quantity','INT') AS Quantity,
x.value('@Price','MONEY') AS Price
FROM OrderXML
CROSS APPLY ItemData.nodes('/Order') o(h)
CROSS APPLY h.nodes('Item') i(x)

/*为xml auto加入根节点*/
SELECT OrderNumber, CustomerID
FROM OrderHeader
FOR XML AUTO, ROOT('SalesOrder')

/*将for xml auto 返回的xml中的列作为元素而不作为属性*/
SELECT
[Order].OrderNumber, [Order].OrderDate,
Customer.CustomerNumber, Customer.Name
FROM OrderHeader [Order]
INNER JOIN Customers Customer ON [Order].CustomerID = Customer.CustomerID
FOR XML AUTO, ELEMENTS

/*for xml raw和for xml auto 比起来可以定义元素名，而auto只能使用表名或者别名，当出现多表查询时，auto会为
每个表产生一个top-level节点，而raw只产生一个，其他表的列也被列为属性*/
SELECT OrderNumber, CustomerID
FROM OrderHeader
FOR XML RAW('Order')

/*for xml explicit*/
--此句的values部分用的是row constructor，是2008新特性
select 1 as tag ,null as parent,null as 'root!1!',null as 'info!2!id',null  as 'info!2!name!ELEMENT'
union all
select 2 as tag,1 as parent,null,id ,name
from (values(1,'hi'),(2,'hello'),(3,'fuck')) as a(id,name) for xml explicit

/*for xml explicit返回的xml是按照select的返回顺序来组成的，使用xml中不需要的列进行排序，最后拼装xml*/
SELECT
1 AS Tag,
NULL AS Parent,
CustomerNumber AS ‘Order!1!CustNo',
OrderNumber AS ‘Order!1!OrderNo',
REPLACE(STR(OrderID,4) + STR(0,4),' ‘,'0') AS ‘Order!1!Sort!HIDE',
NULL AS ‘LineItems!2!ItemNo',
NULL AS ‘LineItems!2!Qty'
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
NULL,
REPLACE(STR(OrderID,4) + STR(OrderDetailID,4),' ‘,'0'),
i.ItemNumber,
o.Quantity
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
ORDER BY ‘Order!1!Sort!HIDE'
FOR XML EXPLICIT, ROOT('Orders')

/*for xml explict xml directive会保留值的xml值的xml原样，以下返回的xml将包含info节点，如果将xml指令
换成element,则会将info节点的<>转换成&lt;和&gt;*/
/*
ELEMENT directive encodes XML tags
<MyData>&lt;Info about="XML"/&gt;</MyData>
*/
/*
XML directive preserves XML tags
<MyData>
<Info about="XML" />
</MyData>
*/
SELECT
1 AS Tag,
NULL AS Parent,
'<Info about="XML"/>' AS 'MyData!1!!XML'
FOR XML EXPLICIT

/*The XMLTEXT directive wraps the column content in a single tag and integrates it with the rest of the
document，此结果将去掉info结点，并将它的属性整合到mydata节点中*/
/*
<MyData about="XML"></MyData>
*/
SELECT
1 AS Tag,
NULL AS Parent,'<Info about="XML"/>' AS 'MyData!1!!XMLTEXT'
FOR XML EXPLICIT

/*The CDATA directive wraps the value within a CDATA block in the output XML document. EXPLICIT
is the only directive that can generate a CDATA section*/
/*
<MyData><![CDATA[<Info about="XML"/>]]></MyData>
*/
SELECT
1 AS Tag,
NULL AS Parent,
'<Info about="XML"/>' AS 'MyData!1!!CDATA'
FOR XML EXPLICIT


/*for xml path*/
/*
<Orders>
<Order OrderNumber="SO102">
<Customer CustomerNumber="J001" />
<LineItems>
<Item ItemNo="D001" Qty="1" />
</LineItems>
</Order>
</Orders>
*/
SELECT
oh.OrderNumber AS '@OrderNumber',
c.CustomerNumber AS 'Customer/@CustomerNumber',
i.ItemNumber AS ‘LineItems/Item/@ItemNo',
od.Quantity AS ‘LineItems/Item/@Qty'
FROM OrderHeader oh
INNER JOIN Customers c ON oh.CustomerID = c.CustomerID
AND OrderID = 2
INNER JOIN OrderDetails od ON od.OrderID = oh.OrderID
INNER JOIN Items i ON i.ItemID = od.ItemID
FOR XML PATH('Order'),ROOT('Orders')

/*a ‘‘mixed’’ type element (an element that has a text value as well as attributes) can
be created by naming a column with an asterisk (‘‘*’’)*/
/*
<Orders>
<Order CustomerID="1">SO101</Order>
<Order CustomerID="1">SO102</Order>
</Orders>
*/
SELECT
CustomerID AS ‘@CustomerID’,
OrderNumber AS ‘*’
FROM OrderHeader
FOR XML PATH(’Order’), ROOT(’Orders’)

/*The data() indicator can also be used to generate a space-separated list of values by making the PATH
name empty*/
/*
<Items>D001 Z001</Items>
*/
SELECT
ItemNumber AS ‘data()’
FROM Items
FOR XML PATH(’’), ROOT(’Items’)

/*the text() indicator along with empty PATH name will generate a similar string, but without
spaces between the values*/
/*
<Items>D001Z001</Items>
*/
SELECT
ItemNumber AS ‘text()’
FROM Items
FOR XML PATH(’’), ROOT(’Items’)

/*a comment can be generated using the special column name indicator comment() along with
FOR XML PATH*/
/*
<Orders>
<!--Order Number-->
<OrderNumber>SO101</OrderNumber>
<!--Customer ID-->
<CustomerID>1</CustomerID>
</Orders>
*/
SELECT
‘Order Number’ AS ‘comment()’,
OrderNumber,
‘Customer ID’ AS ‘comment()’,
CustomerID
FROM OrderHeader WHERE OrderID = 1
FOR XML PATH(’’), ROOT(’Orders’)


/*a nested FOR XML query ,type directive*/
/*<SalesOrder OrderNumber="SO101">
<Customer CustomerNumber="J001" Name="Jacob Sebastian"/>
<Items>
<Item ItemNumber="D001" Quantity="1" Price="900.0000" />
<Item ItemNumber="Z001" Quantity="1" Price="200.0000" />
</Items>
</SalesOrder>*/
SELECT
SalesOrder.OrderNumber,
SalesOrder.OrderDate,
( SELECT CustomerNumber, Name
FROM Customers Customer
FOR XML AUTO, TYPE ),
( SELECT ItemNumber, Quantity, Price FROM
(
SELECT
i.ItemNumber,
o.Quantity,
o.Price
FROM Items i
INNER JOIN OrderDetails o ON i.ItemID = o.ItemID
WHERE OrderID = 1
) Item
FOR XML AUTO, ROOT(’Items’),TYPE )
FROM OrderHeader SalesOrder
WHERE OrderID = 1
FOR XML AUTO

/*Because the TYPE directive generates an XML data type value, instead of NVARCHAR(MAX), the result
of a FOR XML query that uses the TYPE directive can be used as input for other XML operations.*/
SELECT
(
SELECT OrderID, CustomerID
FROM OrderHeader
FOR XML AUTO, TYPE
).value(’(OrderHeader/@OrderID)[1]’,’INT’) AS OrderID


/*XSINIL is applicable only to elements, and can
be used only with the ELEMENTS directive. When XSINIL is specified, FOR XML generates an empty
element for any column that has a NULL value*/
/*
<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<OrderNumber>SO101</OrderNumber>
<CustomerID>1</CustomerID>
<CustomerPhone xsi:nil="true" />
</Order>
*/
SELECT
OrderNumber, CustomerID, NULL AS CustomerPhone
FROM OrderHeader [Order]
FOR XML AUTO, ELEMENTS XSINIL


/*产生xml schema只能针对auto 和raw两个选项，path和explicit不能使用*/

/*XDR schema*/
SELECT
OrderNumber, CustomerID
FROM OrderHeader [Order]
FOR XML AUTO, XMLDATA

/*schema*/
SELECT
OrderNumber, CustomerID
FROM OrderHeader [Order]
FOR XML AUTO, XMLSCHEMA

/*xml schema with target namespace*/
SELECT
OrderNumber, CustomerID
FROM OrderHeader [Order]
FOR XML AUTO, XMLSCHEMA(’urn:some-namespace’)


/*xml namespace*/
/*default namespace*/
/*
<Orders xmlns="http://www.sqlserverbible.com/orders">
<Order CustomerID="1">SO101</Order>
<Order CustomerID="1">SO102</Order>
</Orders>
*/
WITH XMLNAMESPACES(
DEFAULT ‘http://www.sqlserverbible.com/orders’
)
SELECT
CustomerID AS ‘@CustomerID’,
OrderNumber AS ‘data()’
FROM OrderHeader
FOR XML PATH(’Order’),ROOT(’Orders’)

/*multiple namespaces*/
/*
<Orders xmlns:ord="http://www.sqlserverbible.com/orders"
xmlns:cust="http://www.sqlserverbible.com/customers">
<ord:OrderNumber>SO101</ord:OrderNumber>
<cust:CustomerID>1</cust:CustomerID>
</Orders>
*/
WITH XMLNAMESPACES(
‘http://www.sqlserverbible.com/customers’ AS cust,
‘http://www.sqlserverbible.com/orders’ AS ord
)
SELECT
OrderNumber AS ‘ord:OrderNumber’,
CustomerID AS ‘cust:CustomerID’
FROM OrderHeader WHERE OrderID = 1
FOR XML PATH(’’),ROOT(’Orders’)


/*xml data type's query() function.The result of the query() method is always an XML data type value*/
/*
<Item ItemNumber="D001" Quantity="1" Price="900" />
<Item ItemNumber="Z001" Quantity="1" Price="200" />
<Item ItemNumber="D001" Quantity="1" Price="900" />
*/
SELECT
ItemData.query(’/Order/Item’)
FROM OrderXML


/*flwor*/
/*
<ItemNumber>1001</ItemNumber>
<ItemNumber>1003</ItemNumber>
<ItemNumber>1004</ItemNumber>
*/
DECLARE @x XML
SELECT @x = ‘
<Items>
<ItemNumber>1003</ItemNumber>
<ItemNumber>1004</ItemNumber>
<ItemNumber>1001</ItemNumber>
<ItemNumber>2007</ItemNumber>
<ItemNumber>3009</ItemNumber>
<ItemNumber>4005</ItemNumber>
</Items>’
SELECT
@x.query(’
for $item in Items/ItemNumber
where $item[. < "2000"]
order by $item
return $item ‘)


/*A FLWOR operation can be used to completely restructure an XML document*/
/*
<ItemNumber>D001</ItemNumber>
<ItemNumber>Z001</ItemNumber>
*/
DECLARE @x XML
SELECT @x = ‘
<Item ItemNumber="D001" Quantity="1" Price="900" />
<Item ItemNumber="Z001" Quantity="1" Price="200" />’
SELECT
@x.query(’
for $item in Item
return
<ItemNumber>
{data($item/@ItemNumber)}
</ItemNumber> ‘)

/*SQL Server 2008 adds support for the let clause in FLWOR operations. The let clause allows declaring
and using inline variables within the XQuery expression used in a FLWOR query*/
/*
<Item>
<ItemNumber>D001</ItemNumber>
<TotalPrice>1800</TotalPrice>
</Item>
<Item>
<ItemNumber>Z001</ItemNumber>
<TotalPrice>600</TotalPrice>
</Item>
*/
DECLARE @x XML
SELECT @x = ‘
<Item ItemNumber="D001" Quantity="2" Price="900" />
<Item ItemNumber="Z001" Quantity="3" Price="200" />’
SELECT
@x.query(’
for $item in Item
let $itm := $item/@ItemNumber
let $tot := $item/@Quantity * $item/@Price
return
<Item>
<ItemNumber>{data($itm)}</ItemNumber>
<TotalPrice>{data($tot)}</TotalPrice>
</Item>
‘)

/*Insert operation*/
/*
<SalesOrder OrderNumber="SO101">
<CustomerID>1</CustomerID>
</SalesOrder>
*/
DECLARE @x XML
SELECT @x = ‘<SalesOrder OrderNumber="SO101"/>’
DECLARE @CustomerID INT
SELECT @CustomerID = 1
SET @x.modify(’
insert element CustomerID {sql:variable("@CustomerID")}
as last into (SalesOrder)[1]
‘)
SELECT @x



/*Update operation*/
/*
<SalesOrder OrderNumber="SO101">
<CustomerID>2</CustomerID>
</SalesOrder>
*/
DECLARE @x XML
SELECT @x = ‘
<SalesOrder OrderNumber="SO101">
<CustomerID>1</CustomerID>
</SalesOrder>’
DECLARE @CustomerID INT
SELECT @CustomerID = 2
SET @x.modify(’
replace value of (SalesOrder/CustomerID/text())[1]
with sql:variable("@CustomerID")
‘)
SELECT @x


/*Delete operation*/
/*
<SalesOrder OrderNumber="SO101" />
*/
DECLARE @x XML
SELECT @x = ‘
<SalesOrder OrderNumber="SO101">
<CustomerID>1</CustomerID>
</SalesOrder>’
SET @x.modify(’
delete (SalesOrder/CustomerID)[1]
‘)
SELECT @x


/*SQL Server 2008 enhanced the modify() method to support XML variables with the insert command.*/
/*
<SalesOrder OrderNumber="SO101">
<CustomerID>1</CustomerID>
<Items>
<Item ItemNumber="Z001" Quantity="1" Price="900" />
</Items>
</SalesOrder>
*/
DECLARE @doc XML, @val XML
SELECT @doc = ‘
<SalesOrder OrderNumber="SO101">
<CustomerID>1</CustomerID>
</SalesOrder>’
SELECT @val = ‘
<Items>
<Item ItemNumber="Z001" Quantity="1" Price="900"/>
</Items>’
SET @doc.modify(’
insert sql:variable("@val")
as last into (SalesOrder)[1]
‘)
SELECT @doc


/*The WITH NAMESPACES directive can be used to declare the XML namespaces and refer to them in the
XQuery expressions following the declaration*/
DECLARE @x XML
SELECT @x = ‘
<SalesOrder xmlns="http://www.sqlserverbible.com/order"
xmlns:cust="http://www.sqlserverbible.com/customer">
<OrderID>1</OrderID>
<cust:CustomerID>10001</cust:CustomerID>
</SalesOrder>’
;WITH XMLNAMESPACES(
DEFAULT ‘http://www.sqlserverbible.com/order’,
‘http://www.sqlserverbible.com/customer’ AS cust
)
SELECT
@x.value(’(SalesOrder/OrderID)[1]’,’INT’) AS OrderID,
@x.value(’(SalesOrder/cust:CustomerID)[1]’,’INT’) AS CustomerID
/*
OrderID CustomerID
----------- -----------
1 10001
*/


/*OPENXML() 这个已经基上应该被淘汰不用了，2000的遗留物，一是因为它不能结合在select中，以一种批处理的方式来
处理数据，另外因为它调用的是com，在内存占用上也是不小，性能不怎么样，下面是一个带命名空间的例子，如果不带
命名空间，则sp_xml_preparedocument不需要最后一个命名空间定义的参数*/
DECLARE @hdoc INT
DECLARE @xml VARCHAR(MAX)
SET @xml =’
<itm:Items xmlns:itm="http://www.sqlserverbible.com/items">
<itm:Item ItemNumber="D001" Quantity="1" Price="900.0000" />
<itm:Item ItemNumber="Z001" Quantity="1" Price="200.0000" />
</itm:Items>’
-- Step 1: initialize XML Document Handle
EXEC sp_xml_preparedocument
@hdoc OUTPUT,
@xml,
‘<itm:Items xmlns:itm="http://www.sqlserverbible.com/items"/>’
-- Step 2: Call OPENXML()
SELECT * FROM OPENXML(@hdoc, ‘itm:Items/itm:Item’)
WITH (
ItemNumber CHAR(4) ‘@ItemNumber’,
Quantity INT ‘@Quantity’,
Price MONEY ‘@Price’
)
-- Step 3: Free document handle
exec sp_xml_removedocument @hdoc

/*
ItemNumber Quantity Price
---------- ----------- ---------------------
D001 1 900.00
Z001 1 200.00
*/


/*一个xml schema collection 创建的例子*/
CREATE XML SCHEMA COLLECTION CustomerSchema AS '
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:attribute name="CustomerID" use="required">
<xs:simpleType>
<xs:restriction base="xs:integer">
<xs:minInclusive value="1"/>
<xs:maxInclusive value="9999"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="CustomerName" use="optional">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="40"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>'
GO

/*Typed XML parameters can be used as input and output parameters of stored procedures. They can also
be used as input parameters and return values of scalar functions.*/
/*创建typed xml变量也就是跟schema绑定的xml变量*/
DECLARE @x XML(CustomerSchema)

/*创建一个typed xml列*/
CREATE TABLE TypedXML(
ID INT,
CustomerData XML(CustomerSchema))

/*xml document and xml content示例，document是指完整的xml，需要有一个顶级节点，content指的是xml片段可以有多个
顶级节点，content是默认值，如果不指定，sqlserver会默认为是content*/
-- XML Document
DECLARE @x XML(DOCUMENT CustomerSchema)
SELECT @x = ‘<Customer CustomerID="1001" CustomerName="Jacob"/>’
-- XML Content
DECLARE @x XML(CONTENT CustomerSchema)
SELECT @x = ‘
<Customer CustomerID="1001" CustomerName="Jacob"/>
<Customer CustomerID="1002" CustomerName="Steve"/>’


/*xml schema collection中有多个xml schema的例子，在含有多个schema的情况下，只要xml和collection中的任何一个
schema相配，就被认为合法*/
CREATE XML SCHEMA COLLECTION CustomerOrOrder AS ‘
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:attribute name="CustomerID"/>
<xs:attribute name="CustomerName"/>
</xs:complexType>
</xs:element>
<xs:element name="Order">
<xs:complexType>
<xs:attribute name="OrderID"/>
<xs:attribute name="OrderNumber"/>
</xs:complexType>
</xs:element>
</xs:schema>’
GO


DECLARE @x XML(CustomerOrOrder)
SELECT @x = ‘<Customer CustomerID="1001" CustomerName="Jacob"/>’
SELECT @x = ‘<Order OrderID="121" OrderNumber="10001"/>’
SELECT @x = ‘
<Customer CustomerID="1001" CustomerName="Jacob"/>
<Order OrderID="121" OrderNumber="10001"/>’



/*对一个现有的schema collection添加新的schema*/
ALTER XML SCHEMA COLLECTION CustomerOrOrder ADD ‘
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Item">
<xs:complexType>
<xs:attribute name="ItemID"/>
<xs:attribute name="ItemNumber"/>
</xs:complexType>
</xs:element>
</xs:schema>’
GO


/*查找是否存一个xml schema collection*/
IF EXISTS(
SELECT name FROM sys.xml_schema_collections
WHERE schema_id = schema_id(’dbo’) AND name = ‘CustomerSchema’
) DROP XML SCHEMA COLLECTION CustomerSchema