sql xml xquery (2)

最新推荐文章于 2025-08-28 11:25:33 发布

转载最新推荐文章于 2025-08-28 11:25:33 发布 · 900 阅读

Sql Server 专栏收录该内容

125 篇文章

订阅专栏

本文介绍了一系列使用SQL对XML数据进行操作的方法，包括查询、修改和插入等操作。通过实例展示了如何利用SQL内置的XML处理功能来高效地管理XML格式的数据。

1,
DECLARE @x xml
SET @x = '<ROOT><a>111</a></ROOT>'
SELECT @x.query('/ROOT/a')

2,
DECLARE @x xml;
SET @x = '';
SELECT @x.query('(1,2, (3,4,5)),6');

3,
DECLARE @x xml;
SET @x = '';
SELECT @x.query('1');
SELECT @x.query('"abc", "xyz"');
SELECT @x.query('data(1)');
SELECT @x.query('<x> {1+2} </x>');

4,
declare @x xml
set @x=''
select @x.query('<a>  {"Hello"}  </a>,

    <b> {"Hello2"}  </b>')

5,
DECLARE @Timesheet TABLE ( TimesheetNo VARCHAR(20), TimesheetXML XML )
INSERT INTO @Timesheet SELECT 'T000001', '<TimeCard xmlns="http://ns.hr-xml.org/2006-02-28"><AdditionalData type="TimeSheetNo">T000001</AdditionalData><AdditionalData type="CostCentreDepartment" /></TimeCard>'
INSERT INTO @Timesheet SELECT 'T000002', '<TimeCard xmlns="http://ns.hr-xml.org/2006-02-28"><AdditionalData type="TimeSheetNo">T000002</AdditionalData><AdditionalData type="CostCentreDepartment" /></TimeCard>'

SELECT 'before', *
FROM @Timesheet

;WITH XMLNAMESPACES( DEFAULT 'http://ns.hr-xml.org/2006-02-28' )
UPDATE @Timesheet
SET TimesheetXML.modify('replace value of (TimeCard/AdditionalData[@type="TimeSheetNo"]/text())[1] with concat( "L", .)')

SELECT 'after', *
FROM @Timesheet

6,
SET NOCOUNT ON
DECLARE  @tbXML TABLE ( ID INT PRIMARY KEY, ParameterValue XML )
DECLARE @i INT

SET @i = 0

INSERT INTO @tbXML VALUES ( 1, '<USER><User ID="1" Name="Billy"/></USER><USER><User ID="2" Name="Billy"/></USER>' )
INSERT INTO @tbXML VALUES ( 2, '<USER><User ID="1" Name="John"/></USER><USER><User ID="2" Name="Billy"/></USER>' )
INSERT INTO @tbXML VALUES ( 3, '<USER><User ID="1" Name="David"/></USER><USER><User ID="2" Name="Billy"/></USER><MANAGER><User ID="1" Name="Billy"/></MANAGER>' )

SELECT 'before', *
FROM @tbXML

WHILE EXISTS ( SELECT * FROM @tbXML WHERE ParameterValue.exist('//User/@Name[.="Billy"]') = 1 )
BEGIN
  UPDATE  @tbXML
  SET     ParameterValue.modify('replace value of (//User/@Name[.="Billy"])[1] with "Peter"')

  SET @i = @i + 1

  -- Safety
  IF @i > 99 BEGIN RAISERROR( 'Too many loops.', 16, 1 ) BREAK END
END

SELECT 'after', *
FROM @tbXML

7,
DECLARE @xmlEntities xml
DECLARE @xmlPackages xml
 
; with xmlnamespaces('l.xsd' as l, 'lx.xsd' as lx)
select @xmlEntities='<l:Entity xmlns:l="l.xsd" />'
,  @xmlPackages='<lx:DataItemPackage xmlns:lx="lx.xsd">
  <lx:PackageMetadata />
  <lx:Digest />
</lx:DataItemPackage>'

select @xmlEntities , @xmlPackages 

 set @xmlPackages.modify('
declare namespace lx="lx.xsd";declare namespace l="l.xsd";
insert sql:variable("@xmlEntities") into (/lx:DataItemPackage/lx:Digest)[1]  ')
SELECT @xmlPackages