Contents

declare @xml xml
set @xml = 
'

	
		첫 번째 값
	
	
		두 번째 값
	

'
select 
	@xml.query('data(/root/row[value = "첫 번째 값"])')
,	@xml.exist('/root/row/value[taxt() = "두 번째 값"]')

create table #xml(x xml);

insert #xml values(
'

	
		첫 번째 값
	
	
		두 번째 값
	

'
);

update #xml
set x.modify('replace value of(/root/row[2]/value/text())[1] with "세 번째 값"');

/*
DROP TABLE ETL_MetaData
CREATE TABLE ETL_MetaData(pkg_name nvarchar(500), dtsx xml)
go

INSERT ETL_MetaData
SELECT 'package2.dtsx', convert(xml, BulkColumn)
FROM OPENROWSET (BULK 'c:\package2.dtsx', SINGLE_BLOB) as t 
*/

select
	PackageName.value('
		declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
		(/DTS:Property)[1]', 'varchar(255)'
	) PackageName
,	Guid.value('
		declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
		(/DTS:Property)[1]', 'nvarchar(255)'
	) Guid 
from (
	select
		dtsx.query('
			declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
			/DTS:Executable[@DTS:ExecutableType="MSDTS.Package.1"]/DTS:Property[@DTS:Name="ObjectName"]') PackageName
	,	dtsx.query('
			declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
			/DTS:Executable[@DTS:ExecutableType="MSDTS.Package.1"]/DTS:Property[@DTS:Name="DTSID"]') Guid
	from etl_metadata
) t

/*
PackageName	Guid
----------- ---------------------------------------
Package2	{FF3B203A-AE42-4890-B82C-39964D8F6481}
추출해요	{85963DBC-3FEC-4A62-9D4E-0882F15F5397}
*/

declare 
	@list nvarchar(100);

set @list = 'a,c';

with x(xitems)
as
(select convert(xml, '' + replace(@list, ',', '') + '') xitems)
select
	y.items.value('.[1]', 'varchar(20)')
from x
	cross apply x.xitems.nodes('/r') as y(items)

declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast((''+replace(@str,@delimiter ,'')+'') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

DECLARE @x varchar(5000)
SET @x = '

	
		34
		10
		4
	
	
		36
		11
		1
	
';

SELECT
	y.item.value('(ColA)[1]', 'varchar(50)') ColA
,	y.item.value('(ColB)[1]', 'varchar(50)') ColB
,	y.item.value('(ColC)[1]', 'varchar(50)') ColC
FROM (SELECT CONVERT(xml, @x) xitem) x
	CROSS APPLY x.xitem.nodes('/Parameters/Row') as y(item)

DECLARE @x varchar(5000)
SET @x = '

	
		34
		10
		4
	
	
		51
		10
		5
	
	
		36
		11
		1
	
';

SELECT
	y.item.value('(GameSeq)[1]', 'varchar(50)') GameSeq
,	y.item.value('(TestType)[1]', 'varchar(50)') TestType
,	y.item.value('(TestDegree)[1]', 'varchar(50)') TestDegree
FROM (SELECT CONVERT(xml, @x) xitem) x
	CROSS APPLY x.xitem.nodes('/Parameters/Row') as y(item)