SQL Server 2005中的T-SQL增强

最新推荐文章于 2022-11-13 15:35:12 发布

MartinWu

最新推荐文章于 2022-11-13 15:35:12 发布

阅读量298

点赞数 2

分类专栏：数据库文章标签： sql server insert xml table arrays openxml

数据库专栏收录该内容

0 篇文章 0 订阅

订阅专栏

丰富的数据类型 Richer Data Types

1、varchar(max)、nvarchar(max)和varbinary(max)数据类型最多可以保存2GB的数据，可以取代text、ntext或image数据类型。
CREATE TABLE myTable
(
id INT,
content VARCHAR(MAX)
)

2、XML数据类型
XML数据类型允许用户在SQL Server数据库中保存XML片段或文档。

错误处理 Error Handling

1、新的异常处理结构

2、可以捕获和处理过去会导致批处理终止的错误
前提是这些错误不会导致连接中断（通常是严重程度为21以上的错误，例如，表或数据库完整性可疑、硬件错误等等。）。

3、TRY/CATCH 构造
SET XACT_ABORT ON
   BEGIN TRY
     <core logic>
   END TRY
   BEGIN CATCH TRAN_ABORT
     <exception handling logic>
   END TRY

@@error may be quired as first statement in CATCH block

4、演示代码
USE demo
GO
--创建工作表

CREATE TABLE student
(
stuid INT NOT NULL PRIMARY KEY,
stuname VARCHAR(50)
)

CREATE TABLE score
(
stuid INT NOT NULL REFERENCES student(stuid),
score INT
)
GO

INSERT INTO student VALUES (101,'zhangsan')
INSERT INTO student VALUES (102,'wangwu')
INSERT INTO student VALUES (103,'lishi')
INSERT INTO student VALUES (104,'maliu')

--调用一个运行时错误
SET XACT_ABORT OFF
BEGIN TRAN
     INSERT INTO score VALUES (101,90)
     INSERT INTO score VALUES (102,78)
     INSERT INTO score VALUES (107, 76) /* 外键错误 */
     INSERT INTO score VALUES (103,81)
     INSERT INTO score VALUES (104,65)
COMMIT TRAN
GO

SELECT * FROM student
SELECT * FROM score

--使用TRY...CATCH构造，并调用一个运行时错误
SET XACT_ABORT OFF
BEGIN TRY
    BEGIN TRAN
       INSERT INTO score VALUES (101,90)
       INSERT INTO score VALUES (102,78)
       INSERT INTO score VALUES (107, 76) /* 外键错误 */
       INSERT INTO score VALUES (103,81)
       INSERT INTO score VALUES (104,65)
    COMMIT TRAN
    PRINT '事务提交'
END TRY
BEGIN CATCH
    ROLLBACK
     PRINT '事务回滚'
        SELECT ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_MESSAGE() as ErrorMessage;
   END CATCH
GO

SELECT * FROM score
GO

快照隔离 Snapshot Isolation

1、写入程序不会阻碍读取程序
2、Snapshot isolation must be enabled for DB
      ALTER DATABASE 数据库 SET allow_snapshot_isolation ON
3、Snapshot isolation must be enabled for connection
      Set transaction isolation level snapshot
4、UPDATE transactions keep old versions of data in a linked list
5、新的隔离级别提供了以下优点：
　 1) 提高了只读应用程序的数据可用性
　 2) 允许在OLTP环境中执行非阻止读取操作
　 3) 可对写入事务进行自动的强制冲突检测
6、演示代码
CREATE DATABASE demo2
GO
USE demo2
   ALTER DATABASE demo2 SET allow_snapshot_isolation ON
   CREATE TABLE test
   (
     tid INT NOT NULL primary key,
     tname VARCHAR(50) NOT NULL
   )
INSERT INTO test VALUES(1,'version1')
INSERT INTO test VALUES(2,'version2')

--连接一

USE demo2
BEGIN TRAN
UPDATE test SET tname='version3' WHERE tid=2
SELECT * FROM test

--连接二
USE demo2
SET transaction isolation level snapshot
SELECT * FROM test

TOP 增强功能

1、TOP 增强
可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。
可以在DELETE、UPDATE和INSERT查询中使用TOP选项。

2、更好地替换SET ROWCOUNT选项，使之更为有效。

OUTPUT

1、SQL Server 2005引入一个新的OUTPUT子句，以使您可以冲修改语句(INSERT、UPDATE、DELETE)中将数据返回到表变量中。

2、新的OUTPUT子局的语法为：
OUTPUT <dml_select_list> INTO @table_variable
可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象，其方式与访问触发器类似。在INSERT语句中，只能访问插入的表。在DELETE语句中，只能访问删除的表。在UPDATE语句中，可以访问插入的表和删除的表。

3、代码演示
USE demo
GO
CREATE TABLE tt
(
id INT IDENTITY,
c1 VARCHAR(15)
)
GO

INSERT INTO tt VALUES ('r1')
INSERT INTO tt VALUES ('r2')
INSERT INTO tt VALUES ('r5')
INSERT INTO tt VALUES ('r6')
INSERT INTO tt VALUES ('r7')
INSERT INTO tt VALUES ('r8')
INSERT INTO tt VALUES ('r9')
INSERT INTO tt VALUES ('r10')

DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
DELETE tt
OUTPUT DELETED.id, DELETED.c1 INTO @del
WHERE id < 3
SELECT * FROM @del
GO
-----------------------------------------------
USE demo
GO
CREATE TABLE toptest (column1 VARCHAR(150))
GO
INSERT INTO toptest VALUES('t1')
INSERT INTO toptest VALUES('t2')
INSERT INTO toptest VALUES('t3')
INSERT INTO toptest VALUES('t4')
INSERT INTO toptest VALUES('t5')
INSERT INTO toptest VALUES('t6')
INSERT INTO toptest VALUES('t7')
INSERT INTO toptest VALUES('t8')
SELECT * FROM toptest
GO

CREATE TABLE toptest2 (column2 VARCHAR(150))
GO
INSERT INTO toptest2 VALUES('c1')
INSERT INTO toptest2 VALUES('c2')

--声明3个变量
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--赋值
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--使用计算表达式
SELECT TOP(@c) * FROM toptest

--使用SELECT语句作为条件
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

--指出top
DELETE TOP(2) toptest where column1>'t6'

--更新top
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'

SELECT * FROM toptest

排序函数 Ranking Functions

1、SQL Server引入几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。

2、排序函数都遵循类似的语法模式：
()OVER
([PARTITION BY]
ORDER BY)
该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。以下详细讨论不同的函数。

3、ROW_NUMBER
ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
USE demo
GO
CREATE TABLE rankorder
(
orderid INT,
qty INT
)
GO
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
GO
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS rank,
DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty

通用表表达式 Common Table Expressions

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。

视图、派生表和CTE内部的查询的一般形式

1、视图
CREATE VIEW <view_name>(<column_aliases>) AS <view_query>

2、派生表
SELECT * FROM (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)

3、CTE
WITH <cte_alias>(<column_aliases>)
AS
{
<cte_query>
)
SELECT * FROM <cte_alias]>
在关键字WITH之后，为CTE提供一个别名，并且为它的结果列提供一个可选的别名列表；编写CTE的主体；然后从外部查询中引用它。

4、演示代码
USE AdventureWorks
GO
WITH SalesCTE(ProductID, SalesOrderID)
AS
(
SELECT ProductID, COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
)
SELECT * FROM SalesCTE

Recursive CTEs 递归的通用表表达式

递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。

--使用递归的通用表表达式
USE demo
GO
CREATE TABLE CarParts
(
CarID INT NOT NULL,
Part VARCHAR(15),
SubPart VARCHAR(15),
Qty INT
)
GO
INSERT CarParts VALUES (1, 'Body', 'Door', 4)
INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
INSERT CarParts VALUES (1, 'Door', 'Window', 1)
INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
GO
SELECT * FROM CarParts
GO

WITH CarPartsCTE(SubPart, Qty)
AS
(
-- 固定成员 (AM):
-- SELECT查询无需参考CarPartsCTE
SELECT SubPart, Qty
FROM CarParts
WHERE Part = 'Body'
UNION ALL
-- 递归成员 (RM):
-- SELECT查询参考CarPartsCTE
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
FROM CarPartsCTE
INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
WHERE CarParts.CarID = 1
)
-- 外部查询
SELECT SubPart, SUM(Qty) AS TotalNUM
FROM CarPartsCTE
GROUP BY SubPart

新的关系运算符 PIVOT/UNPIVOT/APPLY

1、PIVOT
PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。

2、UNPIVOT
UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。

3、APPLY
APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。

演示：

USE demo
GO

CREATE TABLE orders
(
Customer VARCHAR(10) NOT NULL,
product VARCHAR(20) NOT NULL,
quantity INT NOT NULL
)
GO
INSERT orders VALUES('Mike', 'Bike',3)
INSERT orders VALUES('Mike','Chain',2)
INSERT orders VALUES('Mike','Bike',5)
INSERT orders VALUES('Lisa','Bike',3)
INSERT orders VALUES('Lisa','Chain',3)
INSERT orders VALUES('Lisa','Chain',4)
INSERT orders VALUES('Lisa','Bike',2)

SELECT * FROM orders

SELECT * FROM orders
PIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a
USE demo
GO
CREATE TABLE SALES1
(
[Year] INT,
Quarter CHAR(2),
Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES (2001, 'Q1', 80)
INSERT INTO SALES1 VALUES (2001, 'Q2', 70)
INSERT INTO SALES1 VALUES (2001, 'Q3', 55)
INSERT INTO SALES1 VALUES (2001, 'Q3', 110)
INSERT INTO SALES1 VALUES (2001, 'Q4', 90)
INSERT INTO SALES1 VALUES (2002, 'Q1', 200)
INSERT INTO SALES1 VALUES (2002, 'Q2', 150)
INSERT INTO SALES1 VALUES (2002, 'Q2', 40)
INSERT INTO SALES1 VALUES (2002, 'Q2', 60)
INSERT INTO SALES1 VALUES (2002, 'Q3', 120)
INSERT INTO SALES1 VALUES (2002, 'Q3', 110)
INSERT INTO SALES1 VALUES (2002, 'Q4', 180)
GO

SELECT * FROM SALES1
PIVOT
(SUM (Amount) --使用SUM聚合数量列
FOR [Quarter] --PIVOT Quarter 列
IN (Q1, Q2, Q3, Q4)) --使用季节
AS P
GO

SELECT * INTO temp1 FROM orders
PIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a

SELECT * FROM temp1

SELECT customer, product,quantity
FROM temp1
UNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a
----------------------------------------------------
USE demo
GO
CREATE TABLE Arrays
(
aid INT NOT NULL IDENTITY PRIMARY KEY,
array VARCHAR(7999) NOT NULL
)
GO
INSERT INTO Arrays VALUES('')
INSERT INTO Arrays VALUES('10')
INSERT INTO Arrays VALUES('20,40,30')
INSERT INTO Arrays VALUES('-1,-3,-5')
GO
CREATE FUNCTION function1(@arr AS VARCHAR(7999))
RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
AS
BEGIN
DECLARE @end AS INT, @start AS INT, @pos AS INT
SELECT @arr = @arr + ',', @pos = 1,
@start = 1, @end = CHARINDEX(',', @arr, @start)
WHILE @end > 1
BEGIN
INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))

SELECT @pos = @pos + 1,
@start = @end + 1, @end = CHARINDEX(',', @arr, @start)
END
RETURN
END

--测试
SELECT * FROM function1('200,400,300')
GO

SELECT A.aid, F.*
FROM Arrays AS A
CROSS APPLY function1(array) AS F
GO
SELECT A.aid, F.*
FROM Arrays AS A
OUTER APPLY function1(array) AS F
GO

DDL触发器 DDL Triggers

SQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句（例如：CREAT_TABLE、DROP_TABLE等）或者为一组语句（例如：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件）定义DDL触发器。

在DDL触发器内部，可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。

DDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。

代码演示：

USE demo
GO
CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
AS
RAISERROR('没有删除表的权限.', 10, 1)
PRINT '尝试在数据库' + DB_NAME() + '中删除表.'
PRINT CONVERT (nvarchar (1000),EventData())
ROLLBACK
GO
-- 测试
CREATE TABLE TestDROP(col1 INT)
GO
INSERT INTO TestDROP VALUES(1)

DROP TABLE testdrop

-- Server
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
PRINT '发生DDL LOGIN.'
PRINT CONVERT (nvarchar (1000),EventData())
GO

-- 测试
CREATE LOGIN login1 WITH PASSWORD = '123'
ALTER LOGIN login1 WITH PASSWORD = 'xyz'
DROP LOGIN login1

总结

SQL Server 2005中的Transaction-SQL增强功能提高了用户在编写查询时的表达能力，使用户可以改善代码的性能，并且扩充了错误处理能力。

SQL Server 2005 在Transaction-SQL上所做的改进反映了其更好地满足了ANSI-99 SQL规范的要求以及客户的需求。

在Transaction-SQL和托管代码之间的选择。

FOR XML子句

增强                                                   说明
RAW模式下ELEMENTS                 RAW查询可以返回以元素为中心的XML结果
NULL值支持                                    支持null值，可以在一元素为中心的结果中包含空值元素
Inline XSD schemas                      可以生成inline XSD架构
TPYE指明返回xml数据类型值       对于FOR XML查询，可以返回xml数据类型的值，使XML嵌套查询成为可能
PATH模式                                         可以象XPath表达式一样定义XML结果
ROOT标识                                        为结果集指定根元素
Elements命名                                  为RAW和PATH模式查询指定命名元素

FOR XML子句范例

1、RAW模式下ELEMENTS
SELECT SaleID,ProductID,Quantity
FROM Sales
FOR XML RAW,ELEMENTS

2、NULL值支持
SELECT SaleID,ProductID,Quantity
FROM Sales
FOR XML RAW,ELEMENTS XSINIL

3、Inline XSD schemas
SELECT ProductID,Name,ListPrice
FROM Production.Product Product
FOR XML AUTO,XMLSCHEMA

4、TPYE指明返回xml数据类型值
SELECT ProductID,Name,ListPrice
(SELECT saleid,ProductID,Quantity
FROM Sales
WHERE Sales.ProductID-Products.ProductID
FOR XML RAW,TYPE)
FROM Products FOR XML AUTO

5、PATH模式
SELECT ProductID AS "@ProductID",
Name AS "Details/@Name",
Description AS "Details/text()"
FROM products FOR XML PATH

6、ROOT标识
SELECT ProductID,Name,ListPrice
FROM Products FOR XML AUTO,ROOT('PRODUCTS')

7、Elements命名

OPENXML函数

增强                                                       描述
文档可以是xml数据类型值                sp_xml_preparedocument存储过程支持xml参数
WITH子句支持XML数据类型             在WITH子句中，XML类型数据能够被返回
Batch-level scoping                           文档handle在批级有效，当查询批结束后，文档handle也被释放

OPENXML函数范例

declare @mydoc xml
set @mydoc='
<Products>
<Product Category="Book">
    <ID>1</ID>
    <Name>Windows 2003</Name>
    <Vendor>Vendor1</Vendor>
</Product>
<Product Category="Book">
    <ID>2</ID>
    <Name>VS.NET2003</Name>
    <Vendor>Vendor2</Vendor>
</Product>
</Products>'

declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc

SELECT * FROM OPENXML(@docHandle,'/Products/Product',2)
WITH (ID int,Name nvarchar(50),Vendor nvarchar(50))
--------------------------
declare @mydoc xml
set @mydoc='
<Products>
<Product Category="Book" ID="1" Name="Windows 2003" />
<Product Category="Book" ID="2" Name="VS.NET 2003" />
</Products>'

declare @docHandle int
Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc

SELECT * FROM OPENXML(@docHandle,'/Products/Product',1)
WITH (Category nvarchar(50),ID int,Name nvarchar(50))

在数据库中存放XML

优点：
对结构化和非结构化数据实现单一存储
在关系模式中定义可变内容
选择最适合的数据类型

功能：
XML Indexes
基于XQuery的数据检索
基于XQuery的数据修改

XML架构支持：
Typed XML需要架构验证
UnTyped XML需要架构验证

怎样使用Untyped XML

声明xml数据类型
隐式转换字符串
显示转换字符值
使用Convert显示转换字符串
使用well-formed XML

Untyped XML范例

1、声明xml数据类型
CREATE TABLE Invoices
(
InvoiceID INT,
SalesDate DateTime,
CustomerID INT,
ItemList XML
)
DECLARE @itemDoc xml

2、隐式转换字符串值
SET @itemDoc = '<Items>etc.</Items>'

3、显示转换字符串
SET @itemDoc = CAST('<Items>etc.</Items>') AS XML

4、显示CONVERT显示转换字符串
SET @itemDoc = CONVERT(xml,'<Items>etc.</Items>')

5、使用well-formed XML
SET @itemDoc = CONVERT(XML,'<items>etc.')
ERROR!

怎样管理XML架构

1、建立XML架构集合
CREATE XML SCHEMA COLLECTION SalesSchema
AS
'<?xml version="1.0" standalone="yes"?>
<xs:schema id="Sales" xmlns="http://www.gocean.com.cn" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Sales" msdata:IsDataSet="true" msdata:Locale="zh-CN">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Product">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ID" type="xs:int" minOccurs="0" />
              <xs:element name="Name" type="xs:string" minOccurs="0" />
              <xs:element name="Qty" type="xs:int" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
</xs:element>
</xs:schema>
'

2、查看schema信息
SELECT * FROM sys.xml_schema_collections
SELECT * FROM sys.xml_namespaces

3、修改schema集合
ALTER XML SCHMEMA COLLECTION cvSchemas

4、删除schema集合
DROP XML SCHMEMA COLLECTION cvSchemas

怎样使用Typed XML

1、声明typed列或变量
CREATE TABLE HumanResources.EmployeeResume
(
Emplyee INT,
Resume XML (cvSchemas)
)

2、给typed XML赋值
INSERT INTO HumanResources.EmployeeResume
VALUES(1,'<?xml version="1.0" ?>
<resume xmlns="http://cvSchemas">
...</resume>'

3、使用CONTENT或DOCUMET允许/禁止插入片段
CREATE TABLE Orders
(OrderID int IDENTITY(1,1),
CustomerID int,
OrderDetail xml (SalesSchema))

insert orders values(1,'<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>')
--------------------------------
CREATE TABLE Orders
(OrderID int IDENTITY(1,1),
CustomerID int,
OrderDetail xml (DOCUMENT SalesSchema))

insert orders values(1,'<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>
<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>')

管理XML Indexes

1 建立主 XML index
alter table orders
add constraint pk_orders_orderid
primary key clustered(orderid)

CREATE PRIMARY XML INDEX xidx_item
ON Sales.Invoices(ItemList)

CREATE PRIMARY XML INDEX xidx_details
ON orders(details)

2 建立辅助 PATH XML index
CREATE XML INDEX xidx_ItemPath
ON Sales.Invoices(ItemList)
USING XML INDEX xidx_Item FOR PATH

CREATE XML PATH xidx_details_path
ON orders(details)
USING XML INDEX xidx_details FOR PATH

3 建立辅助 PROPERTY XML index
CREATE XML INDEX xidx_ItemProp
ON Sales.Invoices(ItemList)
USING XML INDEX xidx_Item FOR PROPERTY

CREATE XML INDEX xidx_details_property
ON orders(details)
USING XML INDEX xidx_details FOR PROPERTY

4 建立辅助 VALUE XML Index
CREATE XML INDEX xidx_ItemVal
ON Slaes.Invoices(ItemList)
USING XML INDEX xidx_Item FOR VALUE

CREATE XML INDEX xidx_details_value
ON orders(details)
USING XML INDEX xidx_details FOR VALUE

使用 XQuery

1 什么是 XQuery
XQuery 是查询XML数据的语言
/InvoiceList/Invoice[@InvoiceNo=1000]

FLOWER 语句(for,let, order by, where,return)
语句      说明
for         循环通过同属节点
where   应用筛选标准
return   指定xml返回值

使用XQuery表达式 - 演示

declare @mydoc xml
set @mydoc='
<AAA>
<BBB/>
<BBB/>
<CCC>
    <DDD/>
    <BBB>
       <EEE/>
    </BBB>
   </CCC>
</AAA>'

select @mydoc.query('//BBB')
select @mydoc.query('//BBB[1]')
select @mydoc.query('/AAA/BBB[1]')
select @mydoc.query('/AAA/BBB[last()]')

declare @mydoc xml
set @mydoc='
<AAA>
<BBB ID="1"/>
<BBB ID="2"/>
<CCC>
    <DDD/>
    <BBB ID="3">
       <EEE/>
    </BBB>
   </CCC>
</AAA>'

select @mydoc.query('/AAA/BBB[@ID="1"]')

select @myDoc.query('/bookstore/book/title')

查询条件可以是attribute, 也可以是element，如下是element示例
select @myDoc.query('/bookstore/book[price>30]')

declare @myDoc xml
set @myDoc = '
<AAA>
<BBB>HELLO</BBB>
<BBB>Welcome</BBB>
<BBB NAME="NAME1"/>
<CCC ID="1">
    <DDD/>
    <BBB>OK
      <EEE ID="1"/>
    </BBB>
    <BBB/>
   </CCC>
</AAA>
'
select @myDoc.query('
for $id in //BBB
return <result>{data($id)}</result>')

使用XML数据类型的方法

1 Use the query method
SELECT xmlCol.Query(
'<InvoiceNumbers>
{
for $i in .InvoiceList.Invoice
return <InvoiceNo>
{number($i/@InvoiceNum)}
</InvoiceNuo>
}
</InvoiceNumbers>'

select @myDoc.query('
for $id in //BBB
return <result>{data($id)}</result>')

2 Use the value method
SELECT xmlCol.value(
'(/InvoiceList/Invoice/@InvoiceNo)[1]','int')

3 Use the exist method
SELECT xmlCol.exist(
'/InvoiceList/Invoice[@InvoiceNo=1000]'
)

4 Bind relational columns and variables
SELECT Invoices.query(
'<Store>
{sql:column("StoreName")}
</Store>'

使用 Modify 方法修改 XML

1 Use the insert statement
SET @xmlDoc.modify(
'insert element salesperson{"Bill"}
as first
into (/InvoiceList/Invoice)[1]')
------------------------------------------
INSERT
declare @doc xml
set @doc='<Products></Products>'

set @doc.modify(
'insert (<Product><ID>L01</ID><Name>LL01</Name></Product>)
into (/Products)[1]')

set @doc.modify(
'insert (<Product><ID>L02</ID><Name>LL02</Name></Product>)
as first into (/Products)[1]')

set @doc.modify(
'insert (<Product><ID>L03</ID><Name>LL03</Name></Product>)
as last into (/Products)[1]')

set @doc.modify(
'insert attribute Price {"20.50"} into (/Products/Product)[1]')

select @doc
-------------------------------------------

2 Use the replace statement
SET @xmlDoc.modify(
'replace value of
(/InvoiceList/Invoice/SalesPerson/text())[1]
with "Ted"')
-------------------------------------------
set @mydoc.modify('replace value of (/bookstore/book/price/text())[1] with "99.50"')

set @mydoc.modify('replace value of (/bookstore/book/@id)[1] with "10"')

set @mydoc.modify('
replace value of (/bookstore/book/@id)[1]
with(
if(/bookstore/book[@id="1"]) then
"10"
else
"100"
)
')
------------------------------------------

3 Use the delete statement
SET @xmlDoc.modify(
'delete
(/invoiceList/Invoice/SalesPerson)[1]')
-----------------------------------------
declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING" id="1" >
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
</book>
<book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
</book>
<book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
</book>
</bookstore>
'
set @mydoc.modify('delete (/bookstore/book[@id="1"])')
set @mydoc.modify('delete (/bookstore/book[@id="1"])[1]')
set @mydoc.modify('delete (/bookstore/book/price)[1]')
set @mydoc.modify('delete (/bookstore/book/price/text())[1]')
----------------------------------------------------------

使用nodes方法转换XML输出

1 使用query, value和exist方法带xml变量
SELECT nCol.value('@ProductID','int') Product,
  nCol.valus('@Quantity','int') Qty
FROM @xmlOrder.nodes('/Order/ListItem')
AS nTable(nCol)
----------------------------------------------------------
declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING">
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
</book>
<book category="COMPUTER">
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
</book>
<book category="SOFTWARE">
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
</book>
</bookstore>
'
select @myDoc.query('/bookstore/book/title')
-----------------------------
select @myDoc.query('/bookstore/book[price>30]')
select @myDoc.query('for $x in /bookstore/book
where $x/price>30
return $x/title')

select @myDoc.query('for $x in /bookstore/book/title
order by $x
return $x')

select @myDoc.query('for $x in /bookstore/book/title
return <li>{data($x)}</li>')

select @myDoc.query('for $x in /bookstore/book/title
order by $x
return <li>{data($x)}</li>')
----------------------------------------------------------
declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING" id="1" >
    <title>Everyday</title>
    <author>Giade De</author>
    <price>30.00</price>
</book>
<book category="COMPUTER" id="2" >
    <title>Windows 2003</title>
    <author>Mike</author>
    <price>50.00</price>
</book>
<book category="SOFTWARE" id="3" >
    <title>VS.NET2003</title>
    <author>Mike</author>
    <price>90.00</price>
</book>
</bookstore>
'
value查询

select @myDoc.value('(/bookstore/book/@id)[1]','int')

exist查询

select @myDoc.exist('/bookstore/book/title="VS.NET2003"')
select @myDoc.exist('/bookstore/book[@id=1]')
---------------------------------------------------------

结果集中绑定表中列

select orderid,'L01' as ProductID,Customer,
Details.query('
<OrderDetails>
<Customer>{sql:column("Customer")}</Customer>
{
for $x in //row
return $x
}
</OrderDetails>
')
from orders

2 使用APPLY运算符
SELECT nCol.value('../@OrderID[1]','int') ID,
nCol.valus('@ProductID[1]','int') Prod
FROM Sales.Orders
CROSS APPLY OrderDoc.nodes('/Order/ListItem')
AS nTable(nCol)

MartinWu

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 2005中的T-SQL增强

丰富的数据类型 Richer Data Types 1、varchar(max)、nvarchar(max)和varbinary(max)数据类型最多可以保存2GB的数据，可以取代text、ntext或image数据类型。CREATE TABLE myTable( id INT, content VARCHAR(MAX))2、XML数据类型XML数据类型允许用户在SQL Ser
复制链接

扫一扫