SQL Server中对XML操作
1. 检查XML中节点是否存在
SELECT xmlContent.exist('/Samples/Sample'),
* FROM xmlTable
WHERE xmlContent.exist('/Samples/Sample') = 1
SELECT xmlContent.exist('/Samples//Name'),
* FROM xmlTable
WHERE xmlContent.exist('/Samples//Name') = 1
2. 检查节点值是否存在
SELECT TOP 100 *
FROM xmlTable
WHERE xmlContent.exist('/Samples/Sample[Value=1]') = 1
3. 获取XML某个节点的内容 (类似DOM的OutterXML)
SELECT TOP 100 xmlContent.query('/Samples/Sample/Name'),
*
FROM xmlTable
4. 获取指定XML块的内容 (类似DOM的innerText)
SELECT TOP 100 xmlContent.value('(/Samples/Sample/Name)[1]', 'varchar(100)'),
*
FROM xmlTable
附：做测试用的数据表和数据的生成SQL
-- 创建测试表
CREATE TABLE [xmlTable](
[xmlId] [int] IDENTITY(1,1) NOT NULL,
[xmlName] [varchar](50) NOT NULL,
[xmlContent] [xml] NOT NULL,
[xmlDescr] [varchar](2000) NULL
)
-- 初始化数据
-- truncate table xmlTable
DECLARE @iRow int,
@xmlContent xml,
@xmlName varchar(50),
@xmlId int
SET @iRow = 0
SELECT @xmlId = max(xmlid) from xmlTable
if(@xmlId is null)
SET @xmlId = @iRow + 1
While @iRow < 5
BEGIN
SET @xmlName = 'Sample' + Convert(varchar(10), @xmlId + @iRow)
SET @xmlContent = '<Samples><Sample><Name>' + @xmlName+ '</Name><Value>'+ CONVERT(varchar(20), @xmlId + @iRow)
+ '</Value></Sample></Samples>'
INSERT INTO [xmlTable]
([xmlName]
,[xmlContent]
--,[xmlDescr]
)
VALUES
(@xmlName
,@xmlContent
--,<xmlDescr, varchar(2000),>
)
SET @iRow = @iRow + 1
END
