SQL Server XML应用实例-优快云博客

本文链接：https://blog.youkuaiyun.com/liangCK/article/details/3335158

本文展示了如何在SQL Server中使用XML查询来处理字符串，包括获取指定数量的分隔字符串并求和，以及对XML数据中特定元素进行加密和解密的操作。示例还包含了XML查询和节点操作的实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Author: liangCK 小梁

Date : 2008-11-19

示例一: 如在一个 10/20/30 /50/40/90的字符串当中.需要得到前N个”/”的字串,或得到前N个”/”的和.如上的字符串.如果得到前3个”/”的字符串.也就是10/20/30/,要得到前3个”/”的数的和,就是60.这样的操作在以前通常会写一个函数,然后循环记数得到前N个字符串或前N个数的和.现在使用xml query,这样的操作将变得简单

--生成测试数据

DECLARE @t TABLE(data varchar(50))

INSERT @t VALUES(' 10/20/30 /50/40/90')

INSERT @t VALUES(' 1/2/3 /5/4/9')

INSERT @t VALUES(' 10/5/60 /33/12/1')

INSERT @t VALUES(' 10/8/3 /60/55/20')

DECLARE @i int;

--得到前N项

SET @i=3

--1得到前N项的和

SELECT

CAST(('<root><v>'

+REPLACE(data,'/','</v><v>')

+'</v></root>'

) AS xml).query('

//v[position()<=sql:variable("@i")]').value('sum(v)','int')

FROM @t

data

-----------

(4 行受影响)

原理:

将 10/20/30 /50/40/90生成一个

<root>

</root>

这样的数据

然后通过position()得到前几项.然后再通过sum进行求和

--2.得到前N项的字符串

SELECT

CAST((CAST(('<root><v>'

+REPLACE(data,'/','</v><v>')

+'</v></root>'

)AS xml).query('

//v[position()<=sql:variable("@i")]').query('for $i in //v

return concat(string($i),"/")'))

AS varchar(30))

FROM @t

原理跟上面的差不多

data

------------------------------

10/ 20/ 30 /

1/ 2/ 3 /

10/ 5/ 60 /

10/ 8/ 3 /

(4 行受影响)

--示例二:对存储在数据库中的xml的某一个元素进行加密

例如现在有一个这样的xml数据

<Address>广东中山市石岐区</Address>

</Person>

</Persons>

这样的一个xml数据.我需要对Address元素的text进行加密.而其它的可以显示供用户查看.

方法:通过xquery得到Address的元素值.然后再通过加密方法.(加密方法可以看我的另一文章http://blog.youkuaiyun.com/liangCK/archive/2008/10/10/3051814.aspx) 然后再通过xml的modify方法进行替换.解密的方法一样.

DECLARE @var xml;

SET @var=N'

<Address>广东中山市石岐区</Address>

</Person>

</Persons>';

DECLARE @data nvarchar(200);

DECLARE @vardata varbinary(8000);

DECLARE @data_enc varbinary(8000);

--得到Address

SET @data=(SELECT

@var.value('(/Persons/Person[@ID="123456"]/Address)[1]','nvarchar(200)'));

SET @vardata=CAST(@data AS varbinary(8000));

--加密

SET @data_enc=ENCRYPTBYPASSPHRASE(N'密码',@vardata)

--通过replace value of..with 进行替换

SET @var.modify('

replace value of (/Persons/Person[@ID="123456"]/Address/text())[1]

with sql:variable("@data_enc")

--此时查看结果

SELECT @var

<Address>AQAAAPImUfUO9MOLH0GtBq 25L 669QhhcvbeyQUZYzLKbn9t8R8MuaczPjFM=</Address>

</Person>

</Persons>

--解密

SET @vardata=(SELECT

@var.value('(/Persons/Person[@ID="123456"]/Address)[1]','varbinary(8000)'));

SELECT CAST(DECRYPTBYPASSPHRASE(N'密码',@vardata) AS nvarchar(200))

-----------------

广东中山市石岐区

示例三:这是几个xml查询示例:

DECLARE @exampleXml xml

SELECT @exampleXml =

'<?xml version="1.0"?>

</Coverage>

</Quote>

</Coverage>

</Quote>

</Coverage>

</Quote>

</Submission>'

SELECT x.value('../@id','int') AS id,

x.value('local-name(.)','varchar(20)') AS localname,

x.value('.','varchar(20)') AS value

FROM @exampleXml.nodes('/Submission/Quote/*[local-name()!="Coverage"]') AS T(x)

id localname value

----------- -------------------- --------------------

1 Premium 1000

1 Date 1/1/08

1 State CT

2 Premium 1100

2 Date 1/1/08

2 State CT

3 Premium 1200

3 Date 1/1/08

3 State CT

(9 行受影响)

DECLARE @AccountData xml

SELECT @AccountData = '

</Acc>

</Acc>

</AccGrp>

</Acc>

</Acc>

</AccGrp>

</Accounts>'

DECLARE @idoc int;

EXEC sp_xml_preparedocument @idoc OUTPUT,@AccountData

SELECT *

FROM OPENXML(@idoc,'/Accounts/AccGrp')

WITH

(

AccountId varchar(20) '(./Acc[@R="1"]/@ID)[1]',

AccountName varchar(20) '(./Acc[@R="1"]/AccSub/@ID)[1]',

AccountGuid int '(./Acc[@R="24"]/@ID)[1]',

AccountParentId int '(./Acc[@R="4"]/@ID)[1]',

AccountParentName varchar(20) '(./Acc[@R="4"]/AccSub/@ID)[1]'

)

EXEC sp_xml_removedocument @idoc

AccountId AccountName AccountGuid AccountParentId AccountParentName

-------------------- -------------------- ----------- --------------- --------------------

Teller Teller Inc 930 555 Teller Group Inc.

Moter Moter Inc 60605 430 Moter Group Inc

(2 行受影响)

DECLARE @var XML

SET @var = '

<CompanyName>Wide World Importers</CompanyName>

</OrderLine>

<SKU>WWIRB1-2RO</SKU>

</OrderLine>

</Customer>

<CompanyName> US Importers</CompanyName>

</OrderLine>

<SKU>WWIRB1-3RO</SKU>

</OrderLine>

</Customer>

</Customers>'

;WITH XMLNAMESPACES

(

DEFAULT 'http://blog.youkuaiyun.com/liangck'

)

SELECT

A.x.value('(./CompanyName)[1]','varchar(30)') AS CompanyName,

B.x.value('(./SKU)[1]','varchar(20)') AS SKU,

B.x.value('(./Quantity)[1]','int') AS Quantity

FROM @var.nodes('/Customers/Customer') AS A(x)

OUTER APPLY A.x.nodes('./OrderLine') AS B(x)

CompanyName SKU Quantity

------------------------------ -------------------- -----------

Wide World Importers WWIR375 1

Wide World Importers WWIRB1-2RO 16

US Importers WWIR376 1

US Importers WWIRB1-3RO 16

(4 行受影响)