SQLSERVER2005中的 XML 及基本操作

SQLSERVER2005中的 XML 及基本操作

1      创建XML字段表

1.      创建shcema，注册到SQLServer2005的XMl Schema Collections中

CREATE XML SCHEMA COLLECTION SchemaCol'<xsd:schema xmlns="...">

... schema content ...

</xsd:schema>'

2.      建表语法：

CREATE TABLE XMLTest(ID int, xmlcontent xml(SchemaCol))

2      插入数据

1.      直接字符串

Insert into xmltest(xmlcontent)

Values (‘<Root> AAAa</Root>’)

2.      使用OPENROWSET从文件中加载

INSERT INTO xmltest (xmlcontent)

SELECT * FROM OPENROWSET

(BULK 'C:/DOC.xml', SINGLE_NCLOB)

3.      通过For XML转换数据表

DECLARE @xmlDoc XML

SET @xmlDoc =

(

Select 编码,名称

from 部门基本信息 For XML AUTO,ROOT ('ZLSOFT'), ELEMENTS

)

INSERT INTO xmltest (xmlcontent)  Values

(@xmlDoc)

3      XML列转换

通过CAST和Convert进行转换

l  通过CAST

INSERT INTO xmltest(xmlcontent)

VALUES (CAST(‘<Root> AAAa</Root>’) AS xml))

l  通过CONVERT

INSERT INTO xmltest (xmlcontent)

VALUES (1, CONVERT(xml, ‘<Root> AAAa</Root>’))

l  转换成Char类型

SELECT CONVERT(NVARCHAR(MAX), xmlcontent)

FROM xmltest

4      节点查询

1.      通过Query查询(等同于Oracle 的ExtractValue和XMLQuery)

l  一般查询

SELECT xmlcontent.query('/ZLSOFT/ProcessList/Title')

From xmltest

返回值为：

<Title>输出文件</Title>

l  加上Data函数，去掉节点标记

SELECT xmlcontent.query('data(/ZLSOFT/ProcessList[1]/Title)')

From xmltest

返回值为：

输出文件

l  加上命名空间

SELECT xmlcontent.query(

'declare namespace s="http://microsoft/office2007";

/ZLSOFT/ProcessList/Title ') FROM xmltest

2.      通过Value查询（等同于Oracle的XMLCast，只允许单个返回值）

SELECT xmlcontent.value('(/ZLSOFT/ProcessList/Title)[1]','nvarchar(MAX)')

From xmltest

3.      通过exist进行组合查询（等同于Oracle的ExistsNodes）

SELECT xmlcontent.query('data(/ZLSOFT/ProcessList[1]/Title)') FROM xmltest

WHERE xmlcontent.exist('/ZLSOFT/ProcessList/Title') = 1

4.      使用nodes函数（等同于oracle 的XMLTable函数）

用于将 xml 数据类型实例拆分为关系数据，标识将映射到新行的节点。

SELECT NewTable.Instance.query('.')

FROM   xmltest

CROSS APPLY xmlcontent.nodes('/ZLSOFT/ProcessList') as NewTable(Instance)

 

5      节点修改（插入、删除、更新）

语法：

xml-column.modify('insert-query' | 'delete-query' | 'replace-query')

 

1.      删除

UPDATE XMLTest SET xmlcontent.modify

('delete /ZLSOFT/ProcessList[1]/Title')

2.      插入

语法：

xml-column.modify('insert new-content

{as first | as last} into | before | after

xpath-expression')

 

UPDATE XMLTest SET

xmlcontent.modify('insert <newelement>新节点</newelement>

as first into (/ZLSOFT/ProcessList[1]/Title)[1]')

 

3.      修改

语法：

xml-column.modify('replace value of xpath-expression with new-value')

UPDATE XMLTest SET

xmlcontent.modify('

 replace value of (/ZLSOFT/ProcessList[3]/Title)[1] with

"修改节点" cast as xs:string ?')

或

UPDATE XMLTest SET

xmlcontent.modify('

 replace value of (/ZLSOFT/ProcessList[3]/Title)[1]

with xs:string("修改节点")')

6      引用NameSpace

在Sql前加入WITH XMLNAMESPACES ('http://abc' as xmlnamespace )即可

WITH XMLNAMESPACES (‘http://abc’ as zlsoft)

SELECT XMLContent

FROM XMLTest

 

WITH XMLNAMESPACES (‘http://abc’ as zlsoft )

INSERT INTO XMLTest VALUES('<zlsoft:root/>')