以下为学习记录，参照SQL Server 2008 联机丛书资料及其他书籍，以前知道sql中可以处理XML数据，但是没有了解得很肤浅,由于现在很多web站点提供API，但是返回的数据大都是XML格式的，解析也主要是用C#解析的! 但是我想如果把xml放在数据用sql来解析，在生成表是不是更好呢?（说实话，实际项目中还没有用XML字段呢!）先学习记录!
USE Northwind
GO
/**
新建表, EmployeedetailsXML字段使用XML类型
*/
CREATE TABLE XMLEmployeeTable
(
EmployeeId INT ,
EmployeedetailsXML XML
)
GO
/**
插入测试数据
*/
INSERT INTO XMLEmployeeTable
VALUES ( 1, '<ROOT><Employee Name="Harvey" Age="22" Sex="Male" /></ROOT>' )
INSERT INTO XMLEmployeeTable
VALUES ( 2,
CAST('<ROOT><Employee Name="Jinho" Age="20" Sex="Female" /></ROOT>' AS XML) )
GO
SELECT *
FROM XMLEmployeeTable
/**
使用XQuery 查询
*/
SELECT EmployeeId ,
EmployeedetailsXML.query('/ROOT/Employee') FROM dbo.XMLEmployeeTable
GO
/**
判断Employee 节点是否存在Age 属性
*/
SELECT EmployeedetailsXML.exist('/ROOT/Employee[@Age]') AS '是否存在该属性' FROM dbo.XMLEmployeeTable
/**
请注意，各种路径表达式都指定“[1]”以要求每次只返回单个目标。这样就确保了只有单个目标节点
为XML节点添加属性插入重复的属性则报错[可用上面语句判断一下]
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('insert attribute City{"ChengDu"} as first into (/ROOT/Employee)[1]')
GO
/**
添加子节点节点[可以插入重复的节点]
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('insert <Province>SiChuang</Province> as last into (/ROOT/Employee)[1]')
/**
插入文本节点
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('insert text{"Hello world"} as last into (/ROOT/Employee/Province)[1]')
/**
使用XQuery查询
*/
SELECT EmployeedetailsXML.query('/ROOT/Employee')
FROM XMLEmployeeTable ;
-- 替换[把XMLEmployeeTable表中EmployeedetailsXML字段中Employee节点中Age的值修改为]
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('replace value of (/ROOT/Employee/@Age)[1] with "20" ')
--把Province的文本值修改为sichuang
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('replace value of (/ROOT/Employee/Province[1]/text())[1] with "sichuang" ')
--使用IF判断后，修改节点属性值
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify(' replace value of (/ROOT/Employee/@Age)[1]
with (
if ((/ROOT/Employee/@Age) = 20) then
"22"
else
"20"
)
')
/**
[删除节点中的属性]
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('delete(/ROOT/Employee/@City)[1]')
/**
删除文本节点
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('delete(/ROOT/Employee/Province/text())[1]')
/**
删除节点元素Province
*/
UPDATE XMLEmployeeTable
SET EmployeedetailsXML.modify('delete(/ROOT/Employee/Province)')
以下了解指向 “SQL Server 2008 联机丛书”
插入 (XML DML)
delete (XML DML)
query() 方法（xml 数据类型）
替换 (XML DML) 的值
官方示例1DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID VARCHAR(10),
ContactName VARCHAR(20))
官方示例2DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID INT '../@OrderID',
CustomerID VARCHAR(10) '../@CustomerID',
OrderDate DATETIME '../@OrderDate',
ProdID INT '@ProductID',
Qty INT '@Quantity')
官方示例3DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
官方示例4DECLARE @myDoc XML
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
</Features>
</ProductDescription>
</Root>'
SELECT @myDoc
-- insert first feature child (no need to specify as first or as last)
SET @myDoc.modify('
insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
into (/Root/ProductDescription/Features)[1]')
SELECT @myDoc
-- insert second feature. We want this to be the first in sequence so use 'as first'
SET @myDoc.modify('
insert <Warranty>1 year parts and labor</Warranty>
as first
into (/Root/ProductDescription/Features)[1]
')
SELECT @myDoc
-- insert third feature child. This one is the last child of <Features> so use 'as last'
SELECT @myDoc
SET @myDoc.modify('
insert <Material>Aluminium</Material>
as last
into (/Root/ProductDescription/Features)[1]
')
SELECT @myDoc
-- Add fourth feature - this time as a sibling (and not a child)
-- 'after' keyword is used (instead of as first or as last child)
SELECT @myDoc
SET @myDoc.modify('
insert <BikeFrame>Strong long lasting</BikeFrame>
after (/Root/ProductDescription/Features/Material)[1]
')
SELECT @myDoc ;
GO
Technorati 标签: sql,xml
