xml在sql server 2008中应用.doc

XML 在SQL SERVER 2008 中的应用一、XML简单介绍xml 与html的区别： HTML用于显示数据 XML 用于传输和存储数据一个简单的XML文档： -根节点 Welcome to XML!1、 文档第一行声明该文档是一个xml文档，声明了版本(version)和编码方式(encoding)。2、 为根节点，xml数据类型在sql应用中可以省略。3、 标签名或元素名，一般成对出现。当元素内容为空时，可以只有单标签。例如：4、 Welcome to XML! 元素或标签message 的内容。xml中的替代符字符及&是xml的保留字符，xml为区分这些字符，使用内嵌的替代符来标示这些保留字符。如下表：替代符含义例子解析器解释结果⁢3⁢535>353&&A&BA&B'I'mIm"“"no"“no”xml 数据类型在sql应用中的限制请注意以下适用于 xml 数据类型的一般限制： xml 数据类型实例所占据的存储空间大小不能超过 2 GB。 不能用作 sql_variant 实例的子类型。 不支持转换或转换为 text 或 ntext。请改用 varchar(max) 或 nvarchar(max)。 不能进行比较或排序。这意味着 xml 数据类型不能用在 GROUP BY 语句中。 不能用作除 ISNULL、COALESCE 和 DATALENGTH 之外的任何内置标量函数 的参数。 不能用作索引中的键列。但可以作为数据包含在聚集索引中；如果创建了非聚集索 引，也可以使用 INCLUDE 关键字显式添加到该非聚集索引中。二、Xml数据类型 定义xml类型变量 declare xml xmlset xml=select xml创建包含xml类型的列 create table Tb_xml( id int, xml xml)insert into Tb_xml select 1,三、将 XML文档转换为行结果集 OPENXML()函数 OPENXML是一个行级提供程序类似于数据表和视图。OPENXML能够提供内存中xml文档上的行级，是其能够像访问数据库中的数据表一样访问xml数据。openxml语法结构：OPENXML( idoc int in , rowpattern nvarchar in , flags byte in ) WITH ( SchemaDeclaration | TableName ) idoc xml文档内部表式形式的文档句柄，通过xml系统存储过程获取。 rowpattern Xpath路径选择的标准，用来标识要作为行处理的节点。 flages 指示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。 有如下四种取值（该参数为可先输入参数）：取值说明0默认为”以属性为中心”的映射1使用“以属性为中心”的映射。可以与 XML_ELEMENTS 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。2使用“以元素为中心”的映射。可以与 XML_ATTRIBUTES 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。8可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑或）。在检索的上下文中，该标志指示不应将已使用的数据复制到溢出属性 mp:xmltext。其实SQL Server 2008内部是根据第三个参数的二进制比特位上的值来确定查询的方式的。最后2位是00或01（比如：0、1、4、5等）就是以属性为中心进行查询，最后2位是10（比如：2、6、10等）就是以元素为中心的查询，而最后2位是11（比如：3、7等）就表示既要查询属性也要查询元素。 注：如果想要查询出的数据一部分在元素的属性中，一部分在元素的子元素中那么我们可以将该参数换成3.如下示例：示例A：declare mydoc xml -定义xml类型变量获取xml文档数据set mydoc=declare docHandle int -定义获取xml文档句柄的变量Exec sp_xml_preparedocument docHandle OUTPUT,mydoc-通过xml系统存储过程sp_xml_preparedocument获得XML文档的句柄SELECT * FROM OPENXML(docHandle,/Person/row,0)-0表示以属性为中心WITH (FirstName nvarchar(50),MiddleName nvarchar(50),LastName nvarchar(50)EXEC SP_XML_REMOVEDOCUMENT docHandle -释放内存示例B：declare mydoc xmlset mydoc=Windows 2008Vendor1SQL2008Vendor2declare docHandle intExec sp_xml_preparedocument docHandle OUTPUT,mydocSELECT * FROM OPENXML(docHandle,/Products/Product,1)WITH (Category nvarchar(50),Name nvarchar(50),Vendor nvarchar(50)EXEC SP_XML_REMOVEDOCUMENT docHandle注：flages参数为1时是以属性为中心的显示。路径到Product节点以属性为中心进行处理。Product以下的Name和Vendor节点以元素为中心进行处理，所以处理结果不显示。-将示例B中的1换成2显示结果如下注：flages参数为2是以元素为中心显示。路径到Product节点以属性为中心处理不显示，Product以下的Name和Vendor节点以元素为中心处理，并显示出来。-将示例B中的2换成3显示结果如下： OPENXML中xpath路径的使用方法：在您使用 OPENXML 时，会对 XML 文档进行分析，而结果会采用树形式的模型。这个树由节点组成。XPath 表达式用于选择树中的节点。下面的列表描述了一些常用的 XPath 表达式：路径表达式表达式的含义/指明 XML 文档的根节点/指明当前节点的所有子代，包括当前节点.指明 XML 文档的当前节点.指明当前节点的父节点./属性名 或 属性名指明当前节点的具有名称属性名的属性./子级名指明当前节点的那些具有名称子级名的元素的子级示例如下：示例C：DECLARE idoc intDECLARE doc varchar(1000)SET doc =FEDE aaa bbb EXEC sp_xml_preparedocument idoc OUTPUT, docSELECT *FROM OPENXML (idoc, /ROOT/Customer/Order/OrderDetail,3) WITH (Customer varchar(10) ././CustomerID, -最终路径OrderDetail的上两级目录即Customer，字段名可自己定义 ContactName varchar(20) ././ContactName, CustomerID varchar(50) ./CustomerID, -最终路径OrderDetail的上一级目录Order目录下的CustomerID属性 EmployeeID int ./EmployeeID, OrderDate datetime ./OrderDate, OrderID varchar(10) ./OrderID, -或者是Order表示当前目录下的OrderID属性值 ProductID int , -路径可以省略，但字段必须与属性名一样，且区分大小写 Quantity int, OrderDetail varchar(100)., -取当前节点的文档内容 content varchar(100) /) -取跟节点以后的文档内容EXEC SP_XML_REMOVEDOCUMENT idoc结果显示如下：四、将行结果集转换成xml结果集for xml子句的使用 将查询结果以xml文档类型显示出来而不是显示行结果集。通过for xml子句直接研所数据信息。Xml的模式有：RAW,AUTO,PATH和EXPLICIT共四种。 raw模式： RAW 模式将查询结果集中的每一行转换为带有通用标识符 或可能提供元素名称的 XML 元素。默认情况下，行集中非 NULL 的每列值都将映射为 元素的一个属性。如果将 ELEMENTS 指令添加到 FOR XML 子句，则每个列值都将映射到 元素的子元素。示例D：数据表class和stu 中数据如下图：执行语句：select * from class for xml raw执行结果如下图：使用for xml 语句中的RAW参数，也可以查询出以数据表中的信息为标签的结果。执行语句如下：select * from class for xml raw (class),type,elements-type 指定查询以xml类型返回结果(其他模式用法相同)执行结果如下图：AUTO模式： AUTO模式一简单的嵌套xml树返回查询结果集。From子句中的每个表都标示一个xml元素。select 子句中的列出的列映射到相应的元素属性。Auto模式能够产生最可读性的select语句，但是格式控制不够灵活。示例E：Auto模式查询单表数据：执行语句为：select * from class for xml auto执行结果为：Auto模式查询多表数据：执行语句为：select * from stu inner join class on class.CID=STU.CID for xml auto 执行结果如下图：Auto模式也可以使用elements参数是查询的每个字段都以一个元素出现执行语句如下：select * from stu for xml auto,elements执行结果如下图：path模式：Path模式提供一种更简单的方式来混合元素和属性，并引入表示复杂属性的其他嵌套。在 PATH 模式中，列名或列别名被作为 XPath 表达式来处理，而path的参数可用来控制xml格式显示的上级节点。下面用示例来解释一下 示例F：如示例D的class表执行语句如下：select CID,CNAME from class for xml path查询结果为：当查询语句改为：select CID,CNAME from class for xml path(class)查询结果如下：我们可以清楚的看到path的参数改为class后查询结果的上级节点也跟着变为了下面我们在看一条查询：select CID as id,CNAME as name from class for xml path(class)查询结果显示如下：从结果可以看出，随着字段别名的改变元素的名称也跟着改变了。如果我们不给字段别名有会是另一种情况，请看示例执行语句如下：select CID+,CNAME+ from class for xml path()结果显示如下：由于path控制xml格式的灵活性，我们可以用它来做一些格式控制来实现特定格式需要的查询，下面还是用示例来说明。示例G：有一张各地用户信息表Users如下：想通过查询将结果显示为按地点分组的用户以逗号分隔，结果如下：实现语句如下：select FAdress,fname=stuff(select ,+FName from Users where FAdress=A.FAdress for XML path(),1,1,)FROM Users A GROUP BY FAdressClass 表数据执行语句如下：select cast(CID as varchar(10)+,+CNAME+ from class for xml path()查询结果显示如下：EXPLICIT模式explicit模式可显示定义产生的xml树的形状。能够灵活的控制xml树的显示形状。使用这种模式，必须用一种特定的方式编写查询语句，以便显示指定所需嵌套的其他信息。由于其编写查询相对比较复杂，建议采用for xml path 编写会更加灵活简单。示例H：表为示例D的class表字段以属性显示： 执行语句如下：select 1 as tag,null as parent,CID as 班级!1!编号,CNAME as 班级!1!名称 from class for xml explicit结果显示如下：编号字段为属性，名称字段为元素：执行语句如下：select 1 as tag,null as parent,CID as 班级!1!编号, CNAME as 班级!1!名称!xml from class for xml explicit结果显示如下：以多层次显示信息执行语句如下：select 1 as tag,null as parent,CID as 班级!1!编号, CNAME as 班级信息!2!名称!xml from class aunion all select 2 as tag,1 as parent,a.CID,b.CNAME from class a,class b where a.CID=b.CIDorder by 班级!1!编号,tag for xml explicit结果显示如下：五、xml数据类型提供的方法Xml方法使用的限制 xml方法不能直接用在print语句中，可以将方法的结果通过变量接收再print。 group by 子句中不能指定xml数据类型方法。Xml方法在内部是作为子查询来处理，而group by 子句不允许聚合和子查询。Xml数据类型提供的方法有：query()、exist()、value()、nodes()和modify()共五种方法，用法如下：以下表格对各方法做一个简单的描述：方法名描述query执行一个xml查询并返回查询结果exist执行一个xml查询，如果查询有结果则返回1value从xml查询中获得节点或元素的值modify对xml文档中的指定位置做修改nodes将xml数据内容以数据表的形式进行显示以下内容仅讲解xpath表达式在各方法中的应用： (1)、query()方法语法结构为：query(xquery|xpath)该方法允许指定一个xquery或xpath表达式，结果返回一个xml数据类型的数据。query方法用于变量中示例I:执行语句如下：declare mydoc xmlset mydoc=Windows 2008Vendor1SQL2008Vendor2select mydoc.query(Products/Product/Name)语句执行结果如下；query方法取路径当前节点以下的所有内容。query 方法用于数据表的xml类型的列中示例J：-创建包含xml列的表tbcreate table tb(id int,xml xml)-插入数据insert into tb select 1, c# sheng bin gengxin sun 35.99union all select 2,c#sheng gengxin 35.99-查询tb 表中的数据select * from tb表数据为：执行语句如下：select id,xml.query(book/author) as author from tb执行结果如下：展开id为1的author的内容如下：（2）、exist 方法：exist方法用于判断查询是否返回空的结果。使用exist方法可以得到三种值： 0：查询返回一个空结果。 1：查询返回至少一个xml节点。 NULL：标示执行查询的xml数据类型实例名包含NULL。 变量中的应用示例K：declare x xmldeclare f bitset x = set f = x.exist(/root(Somedate cast as xs:date?) eq xs:date(2002-01-02Z)-变量f接收exist方法的值select f结果返回为：0在表列中的应用引用示例I的表，执行语句如下：select * from tb where xml.exist(book/author/last-name)=1查询结果只有一条记录：由于id为2的记录中xml列的数据中不包括last-name节点。Exist方法返回结果为0.（3）、value方法使用value方法获得xml实例中属性或元素的值，该方法只能返回单一的一个值，多于一个将会出错。变量中应用value（）方法同样引用示例H的内容，执行查询如下：select mydoc.value(Products/Product/Vendor)2,varchar(100) as vendor -去当前路径下第二个Vendor节点以后全部文档值结果为显示为：Vendor2select mydoc.value(Products/Product)2,varchar(100) as vendor结果为显示为：SQL2008 Vendor2 表的xml列中应用value（）方法（4）nodes方法nodes方法用于将xml数据内容分离出来，以关系型数据表的xml数据字段进行显示。nodes 方法不能单独使用，必须用于其他四种方法中的一个或在is null或is not null检查中使用。nodes 方法可与value 方法结合使用，实现特定字符分割的字符串的拆分，并以数据表的形式显示。示例如下：示例L：执行语句如下：DECLARE s VARCHAR(100)SET s=a,b,c,dd,ee,f,aa,a,aa,fSELECT b.v FROM(SELECT CAST( + REPLACE(s,) + AS XML) x) a -将s拼接后的语句转换为xml格式CROSS APPLY -a 表交叉连接b表的查询结果，详细资料可在网上查找(SELECT v=t.x.value(.,VARCHAR(10) FROM a.x.nodes(/r)AS t(x) b-nodes方法将节点的数据存放在t表的x字段，通过value方法去除节点的值。结果显示为： （5）modify方法Modify方法用于修改xml文档的内容，可对文档中的内容进行插入，修改和删除操作。对表中xml类型列中内容的增删改，modify方法只能用在update语句的set子句中。modify方法的插入操作：语法：modify（insert expression1 as first|as last|into|after|before expression2） expression1 要插入到文档中的表达式，可以使一个或多个节点，也可以使用单 独的 sql:column()/sql:variable() 函数的非类型化 XML 数据类型实例，或者是一个 XQuery 表达式。 into expression1作为expression2所标示节点的直接后代节点插入。as first或as last用来确定插入节点的位置。 before expression1作为expression2所标示节点的同级节点插入到标示节点的前面。 after expression1作为expression2所标示节点的同级节点插入到标示节点的后面。 expression2 用来标示expression1插入的位置。下面用具体示例来说明：示例：插入节点或属性 DECLARE myDoc xml SET myDoc = -向stu中插入子节点，是第一个子节点可以省略as first和as last SET myDoc.modify(insert woman into (/Root/stu)1)select myDoc -向stu插入第一个节点使用as firstSET myDoc.modify(insert wppas first into (/Root/stu)1)select myDoc-插入stu的同级节点SET myDoc.modify(insert after (/Root/stu)2)select myDoc-向stu中插入属性SET myDoc.modify(insert attribute length10 into (/Root/stu)1)select myDocmodify方法的删除操作语法：delete expression删除节点或属性declare xml xmlset xml= wpp woman -删除sex节点set xml.modify(delete /Root/stu/sex)select xml-删除name节点的值set xml.modify(delete /name/text()select xml-删除stu节点的属性值set xml.modify(delete /stu/id)select xml替代文档中节点或属性的值declare xml xmlset xml= wpp woman wcc -替代name节点的值set xml.modify(replace value of (/name/text()1 with xxx)select xml-替代第一个stu节点的属性id 的值set xml.modify(replace value of (/stu/id)1 with 102)select xml六、应用使用openxml或nodes()方法实现数据表的导入导出。 1）、openxml函数实现数据表的导入导出。创建存储过程P_getdata实现将查询语句的结果集取出，并通过openxml函数将数据导入另一台服务器。 P_getdata存储过程如下： - =- Author:- Create date: 2012-02-08- Description:数据导入导出- =alter procedure dbo.P_GetData1 tag int, -0 表示为单表查询，1表示为多表查询 s varchar(max)as begin set nocount on declare sql varchar(max) declare str varchar(50) if tag=0 set str=(select substring(ltrim(stuff(s,1,PATINDEX(%from%,s)+3,),1,CHARINDEX( ,ltrim(stuff(s+ ,1,PATINDEX(%from%,s)+3,)-1) else set str=Ex_Temp set sql=select * into Ex_Temp from (+s+) m set sql=sql+char(10)+declare str varchar(max), str1 varchar(max) set sql=sql+CHAR(10)+select str=stuff(select ,++ ++( case when =numericOr =decimal then (+convert(varchar(10),a.precision)+,+convert(varchar(10),a.scale)+) when =nvarchar or =nchar then (+convert(varchar(10),a.max_length/2)+) when =varchar or =char or =binary or =varbinary then (+convert(varchar(10),a.max_length)+) when =time or =datetime2 or =datetimeoffset then (+convert(varchar(10),a.scale)+) else end )+CHAR(10) from sys.columns a join sys.types b on a.user_type_id=b.user_type_id where object_id=object_id(Ex_Temp) for xml path(),1,1,) set sql=sql+select str1=STUFF(select ,+name from sys.columns where object_id=object_id(Ex_Temp) for xml path(),1,1,) set sql=sql+CHAR(10)+print DECLARE idoc int+char(10)+EXEC sp_xml_preparedocument idoc OUTPUT,+char(10)+char(10)+ +char(10) set sql=sql+CHAR(10)+print char(10)+char(10)+if OBJECT_ID(+str+) is null create table +str+ ( set sql=sql+CHAR(10)+print str+ )+char(10)+insert into +str+(+str1+)SELECT +str1+char(10)+ FROM OPENXML (idoc, /Records/row, 1) WITH ( +char(10)+str+ )EXEC sp_xml_removedocument idoc set sql=sql+char(10)+select * from Ex_Temp for xml raw set sql=sql+CHAR(10)+drop table Ex_Temp exec (sql) end查询语句及结果集数据如下：select a.FSID,a.FSname,b.CID,b.CNAME from stu a left join class b on a.CID=b.CID数据导出执行代码及执行结果如下：执行代码：exec p_getdata1 1,select a.FSID,a.FSname,b.CID,b.CNAME from stu a left join class b on a.CID=b.CID执行结果：2）、nodes()方法实现数据的导入导出存储过程代码如下：- =- Author:- Create date: - Description:- =alter PROCEDURE P_Getdata tag bit, s varchar(max)ASBEGIN set nocount on; declare sql varchar(max) declare str varchar(50) if tag=0 set str=(select substring(ltrim(stuff(s,1,PA