Contents

1
2 襷り覲 伎
3 螳讌 xpath
4 Well Formed XML?
5 語 襾豪?
6 XQuery: Query(), Exist()
7 Modify()
7.1 replace value of
7.2 insert
7.3 delete
8 XQuery: Query(), Value()
9 XML
10 1
11 2
12 3
13 谿瑚襭


覓語 譴..

蟯碁ゼ 覺.
 @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

1 #

KEB xml朱 れ企覃 螳 .

declare 
    @xml xml

set @xml = '<PEXRATE>
    <螻谿 殊="20190618" 谿="001" 焔螳="082426">
	    <覲貊>S</覲貊>
	    <貊>05</貊>
	    <豌伎>       </豌伎>
	    <牛貊 譬襯="USD">
		    <覲貊>I</覲貊>
		    <IBM牛貊>001</IBM牛貊>
		    <牛覈>US.DLRS   </牛覈>
		    <蟲螳覈>U.S.A.              </蟲螳覈>
		    <覲伎^>2  </覲伎^>
		    <牛螻蟲覿>1</牛螻蟲覿>
		    <TT_BUY蠍讌覿></TT_BUY蠍讌覿>
		    <TT_SEL蠍讌覿></TT_SEL蠍讌覿>
		    <CASH_BUY蠍讌覿></CASH_BUY蠍讌覿>
		    <CASH_SEL蠍讌覿></CASH_SEL蠍讌覿>
		    <CHECK_BUY蠍讌覿></CHECK_BUY蠍讌覿>

		    <蟲覿貊>D</蟲覿貊>
		    <IBM牛貊>001</IBM牛貊>
		    <襷る>0117470</襷る>
		    <谿磯Г>0116554</谿磯Г>
		    <襷れ>0117331</襷れ>
		    <襷れ>0119790</襷れ>
		    <谿磯Г>0120706</谿磯Г>
		    <襷るГ蠍一>0118630</襷るГ蠍一>
		    <ル螳蟆>01186</ル螳蟆>
		    <覩誤一>1000000</覩誤一>
		    <螳襭1>0042403</螳襭1>
		    <螳襭12>0001178</螳襭12>
		    <螳襭9>0001413</螳襭9>
	    </牛貊>
	    <牛貊 譬襯="GBP">
		    <覲貊>I</覲貊>
		    <IBM牛貊>002</IBM牛貊>
		    <牛覈>POUND STR.</牛覈>
		    <蟲螳覈>ENGLAND             </蟲螳覈>
		    <覲伎^>2  </覲伎^>
		    <牛螻蟲覿>1</牛螻蟲覿>
		    <TT_BUY蠍讌覿></TT_BUY蠍讌覿>
		    <TT_SEL蠍讌覿></TT_SEL蠍讌覿>
		    <CASH_BUY蠍讌覿></CASH_BUY蠍讌覿>
		    <CASH_SEL蠍讌覿></CASH_SEL蠍讌覿>
		    <CHECK_BUY蠍讌覿></CHECK_BUY蠍讌覿>

		    <蟲覿貊>D</蟲覿貊>
		    <IBM牛貊>002</IBM牛貊>
		    <襷る>0147245</襷る>
		    <谿磯Г>0145802</谿磯Г>
		    <襷れ>0147135</襷れ>
		    <襷れ>0150219</襷れ>
		    <谿磯Г>0151662</谿磯Г>
		    <襷るГ蠍一>0148732</襷るГ蠍一>
		    <ル螳蟆>01487</ル螳蟆>
		    <覩誤一>1253700</覩誤一>
		    <螳襭1>0027015</螳襭1>
		    <螳襭12>0000740</螳襭12>
		    <螳襭9>0000888</螳襭9>
	    </牛貊>
    </螻谿>
</PEXRATE>
'

select
    convert(date, a.nth.value('@殊', 'varchar(8)')) 殊
,   concat(left(a.nth.value('@焔螳', 'varchar(8)'),2), ':', substring(a.nth.value('@焔螳', 'varchar(8)'), 3, 2), ':', right(a.nth.value('@焔螳', 'varchar(8)'),2)) 螻螳
,   a.nth.value('(覲貊)[1]', 'varchar(8)') 覲貊
,   a.nth.value('(貊)[1]', 'varchar(8)') 貊
,   b.currency_code.value('@譬襯', 'varchar(20)') 譬襯
,   b.currency_code.value('(牛覈)[1]', 'varchar(20)') 牛覈
,   b.currency_code.value('(蟲螳覈)[1]', 'varchar(20)') 蟲螳覈
,   convert(float, b.currency_code.value('(襷るГ蠍一)[1]', 'varchar(20)'))/100 襷るГ蠍一
from @xml.nodes(N'/PEXRATE/螻谿') as a(nth)
    cross apply a.nth.nodes('牛貊') as b(currency_code)
where 1=1
and a.nth.value('@谿', 'varchar(8)') = '001' --1谿襷

2 襷り覲 伎 #

--drop table #dim_account
create table #dim_account(
    account_key int
,   gender xml
,   weekday_name xml
);
go

insert #dim_account values(1, N'<kr></kr><jp></jp>', N'<kr>覈</kr><jp></jp>');
insert #dim_account values(2, N'<kr></kr><jp>錵</jp>', N'<kr></kr><jp>羂</jp>');
go

--襷り覲襯 伎 select
declare @lang char(2)
set @lang = 'kr'

select
    a.account_key
,   b.gender.value('.', 'nchar(1)') gender
,   c.weekday_name.value('.', 'nchar(1)') weekday_name
--,   a.gender.value('kr[1]', 'nchar(1)') gender
from #dim_account a
    cross apply (select a.gender.query('//*[local-name()=sql:variable("@lang")]')) as b(gender)
    cross apply (select a.weekday_name.query('//*[local-name()=sql:variable("@lang")]')) as c(weekday_name)


3 螳讌 xpath #

/*----------------------------------------------------------------------------- 
  Date       : 1 May 2010 
  SQL Version: SQL Server 2005/2008 
  Author     : Jacob Sebastian 
  Email      : jacobvettickal@gmail.com 
  Twitter    : @jacobvettickal  
  Blog       : https://jacobsebastian.com
  Website    : https://jacobsebastian.com

  Summary: 
  This script returns a tabular representation of an XML document

  Modification History:
  Jacob Sebastian - 1 May 2010
		Created the first version
  Jacob Sebatian - 18 June 2010
		Fixed a bug in the XPath Expressiong generated
  Jacob Sebastian - 20 June 2010
		Added new column - ParentName
		Updated the 'treeview' column to show lines
		Added new column - 'Position'
		Added New Column - 'ParentPosition'
  Jacob Sebastian - 23 June 2010		
		Made the function UNICODE compatibile. (Thanks Peso)
  Jacob Sebastian - 30 June 2010		
		Corrected the casing of a few columns to make the function
		work on case sensitive SQL Server installations. 
		(Thanks Rhodri Evans)               
		
Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A 
  PARTICULAR PURPOSE. 
-----------------------------------------------------------------------------*/ 
/* 
SELECT * FROM dbo.XMLTable(' 
<employees> 
    <emp name="jacob"/> 
    <emp name="steve"> 
        <phone>123</phone> 
    </emp> 
</employees> 
') 
*/ 
CREATE FUNCTION [dbo].[XMLTable]( 
    @x XML 
) 
RETURNS TABLE 
AS RETURN 
/*---------------------------------------------------------------------- 
This INLINE TVF uses a recursive CTE that processes each element and 
attribute of the XML document passed in. 
----------------------------------------------------------------------*/ 
WITH cte AS ( 
    /*------------------------------------------------------------------ 
    Anchor part of the recursive query. Retrieves the root element 
    of the XML document 
    ------------------------------------------------------------------*/ 
    SELECT 
        1 AS lvl, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
        CAST(NULL AS NVARCHAR(MAX)) AS ParentName,
        CAST(1 AS INT) AS ParentPosition,
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType, 
        x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath, 
        x.value('local-name(.)','NVARCHAR(MAX)') 
            + N'[' 
            + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR) 
            + N']' AS XPath, 
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,
        x.value('local-name(.)','NVARCHAR(MAX)') AS Tree, 
        x.value('text()[1]','NVARCHAR(MAX)') AS Value, 
        x.query('.') AS this,        
        x.query('*') AS t, 
        CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort, 
        CAST(1 AS INT) AS ID 
    FROM @x.nodes('/*') a(x) 
    UNION ALL 
    /*------------------------------------------------------------------ 
    Start recursion. Retrieve each child element of the parent node 
    ------------------------------------------------------------------*/ 
    SELECT 
        p.lvl + 1 AS lvl, 
        c.value('local-name(.)','NVARCHAR(MAX)') AS Name, 
        CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,
        CAST(p.Position AS INT) AS ParentPosition,
        CAST(N'Element' AS NVARCHAR(20)) AS NodeType, 
        CAST( 
            p.FullPath 
            + N'/' 
            + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX) 
        ) AS FullPath, 
        CAST( 
            p.XPath 
            + N'/' 
            + c.value('local-name(.)','NVARCHAR(MAX)') 
            + N'[' 
            + CAST(ROW_NUMBER() OVER(
				PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
				ORDER BY (SELECT 1)) AS NVARCHAR	) 
            + N']' AS NVARCHAR(MAX) 
        ) AS XPath, 
        ROW_NUMBER() OVER(
				PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
				ORDER BY (SELECT 1)) AS Position,
        CAST( 
            SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1)
            + c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX) 
        ) AS Tree, 
        CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value, 
        c.query('.') AS this,        
        c.query('*') AS t, 
        CAST( 
            p.Sort 
            + CAST( (lvl + 1) * 1024 
            + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4) 
        ) AS VARBINARY(MAX) ) AS Sort, 
        CAST( 
            (lvl + 1) * 1024 
            + (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT 
        ) 
    FROM cte p 
    CROSS APPLY p.t.nodes('*') b(c)        
), cte2 AS ( 
    SELECT 
        lvl AS Depth, 
        Name AS NodeName, 
        ParentName,
        ParentPosition,
        NodeType, 
        FullPath, 
        XPath, 
        Position,
        Tree AS TreeView, 
        Value, 
        this AS XMLData, 
        Sort, ID 
    FROM cte 
    UNION ALL 
    /*------------------------------------------------------------------ 
    Attributes do not need recursive calls. So add the attributes 
    to the query output at the end. 
    ------------------------------------------------------------------*/ 
    SELECT 
        p.lvl, 
        x.value('local-name(.)','NVARCHAR(MAX)'), 
        p.Name,
        p.Position,
        CAST(N'Attribute' AS NVARCHAR(20)), 
        p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        1,
        SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1) 
			+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'), 
        x.value('.','NVARCHAR(MAX)'), 
        NULL, 
        p.Sort, 
        p.ID + 1 
    FROM cte p 
    CROSS APPLY this.nodes('/*/@*') a(x) 
) 
SELECT 
    ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID, 
    ParentName, ParentPosition,Depth, NodeName, Position,  
    NodeType, FullPath, XPath, TreeView, Value, XMLData
FROM cte2

DECLARE @cars xml;

SET @cars =
'<?xml version="1.0" encoding="UTF-8"?>
<Cars>
  <Car id="1234">
     <Make>Volkswagen</Make>
     <Model>Eurovan</Model>
     <Year>2003</Year>
     <Color>White</Color>
  </Car>
  <Car id="5678">
     <Make>Honda</Make>
     <Model>CRV</Model>
     <Year>2009</Year>
     <Color>Black</Color>
     <Mileage>35,600</Mileage>
  </Car>
</Cars>';

SELECT xpath, value FROM dbo.xmlTable(@cars)
WHERE value is not  null ORDER BY xpath

xpath                      Value

-------------------------------------
Cars[1]/Car[1]/@id         1234
Cars[1]/Car[1]/Color[1]    White
Cars[1]/Car[1]/Make[1]     Volkswagen
Cars[1]/Car[1]/Model[1]    Eurovan
Cars[1]/Car[1]/Year[1]     2003
Cars[1]/Car[2]/@id         5678
Cars[1]/Car[2]/Color[1]    Black
Cars[1]/Car[2]/Make[1]     Honda
Cars[1]/Car[2]/Mileage[1]  35,600
Cars[1]/Car[2]/Model[1]    CRV
Cars[1]/Car[2]/Year[1]     2009


4 Well Formed XML? #

れ 覓語襯 谿瑚.

5 語 襾豪? #


6 XQuery: Query(), Exist() #

declare @xml xml
set @xml = 
'
<root>
	<row>
		<value>豌 覯讌 螳</value>
	</row>
	<row>
		<value> 覯讌 螳</value>
	</row>
</root>
'
select 
	@xml.query('data(/root/row[value = "豌 覯讌 螳"])')
,	@xml.exist('/root/row/value[taxt() = " 覯讌 螳"]')

7 Modify() #

create table #xml(x xml);

insert #xml values(
'
<root>
	<row>
		<value>豌 覯讌 螳</value>
	</row>
	<row>
		<value> 覯讌 螳</value>
	</row>
</root>
'
);

7.1 replace value of #
update #xml
set x.modify('replace value of(/root/row[2]/value/text())[1] with " 覯讌 螳"');

7.2 insert #

7.3 delete #

8 XQuery: Query(), Value() #

/*
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}
*/

9 XML #

declare 
	@list nvarchar(100);

set @list = 'a,c';

with x(xitems)
as
(select convert(xml, '<r>' + replace(@list, ',', '</r><r>') + '</r>') 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(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

10 1 #

DECLARE @x varchar(5000)
SET @x = '
<Parameters>
	<Row>
		<ColA>34</ColA>
		<ColB>10</ColB>
		<ColC>4</ColC>
	</Row>
	<Row>
		<ColA>36</ColA>
		<ColB>11</ColB>
		<ColC>1</ColC>
	</Row>
</Parameters>';

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)

11 2 #

DECLARE @x varchar(5000)
SET @x = '
<Parameters>
	<Row>
		<GameSeq>34</GameSeq>
		<TestType>10</TestType>
		<TestDegree>4</TestDegree>
	</Row>
	<Row>
		<GameSeq>51</GameSeq>
		<TestType>10</TestType>
		<TestDegree>5</TestDegree>
	</Row>
	<Row>
		<GameSeq>36</GameSeq>
		<TestType>11</TestType>
		<TestDegree>1</TestDegree>
	</Row>
</Parameters>';

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)

12 3 #

declare @x xml
set @x = (select * from dbo.譯殊讌 for xml path)
--set @x = (select * from dbo.譯殊讌 for xml auto)
select @x

/*
<row>
  <讌覯>1</讌覯>
  <覿襯>蟆伎</覿襯>
  <讌>讌1</讌>
  <る>讌1  る</る>
  <一>1</一>
</row>
<row>
  <讌覯>2</讌覯>
  <覿襯>蟆伎</覿襯>
  <讌>讌2</讌>
  <る>讌2  る</る>
  <一>3</一>
</row>
*/

select
	y.item.value('(讌覯)[1]', 'int') 讌覯
,	y.item.value('(覿襯)[1]', 'varchar(50)') 覿襯
,	y.item.value('(讌)[1]', 'varchar(50)') 讌
,	y.item.value('(る)[1]', 'varchar(500)') る
,	y.item.value('(一)[1]', 'smallint') 一
from (select convert(xml, @x) xitem) x
	cross apply x.xitem.nodes('/row') as y(item)

13 谿瑚襭 #