#title XML
[[TableOfContents]]

문서 작성 중..

괄호를 잘봐라.
{{{
 @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
}}}

==== 환율 예제 ====
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회차만
}}}

==== 매개변수 이용 ====
{{{
--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)

}}}

==== 여러가지 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
}}}


==== Well Formed XML? ====
다음의 문서를 참고한다. 
 * attachment:Well-Formed_XML과_용어.doc
==== 언제 써먹나? ====

==== 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() = "두 번째 값"]')
}}}

==== Modify() ====
{{{
create table #xml(x xml);

insert #xml values(
'
<root>
	<row>
		<value>첫 번째 값</value>
	</row>
	<row>
		<value>두 번째 값</value>
	</row>
</root>
'
);
}}}

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

===== insert =====

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

==== 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)
}}}

==== 예제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)
}}}

==== 예제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)
}}}

==== 예제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)
}}}
==== 참고자료 ====
 * [http://www.jasonstrate.com/2011/01/xquery-for-the-non-expert-variable-use/ XQuery for the Non-Expert]
 * [http://www.sqlservercentral.com/search/?q=XML+Workshop+&t=a XML Workshop]
 * [http://www.sqlservercentral.com/articles/T-SQL/68927/ Using T-SQL to Transform XML Data to a Relational Format]
 * [http://www.sqlservercentral.com/articles/XML/67388/ Gain Space Using XML data type]
 * [http://beyondrelational.com/blogs/jacob/archive/2007/12/20/xml-workshops.aspx XML Workshops]
 * [http://www.sqlservercentral.com/articles/XML/63890/ A Generic Process to Convert XML Data - Part 2]
 * [http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5xml.asp XML Support in SQL Server 2005]
 * [http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp FOR XML in SQL Server 2005]
 * [http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql25xmlbp.asp XML Best Practices for SQL Server 2005]
 * [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp Performance Optimizations for the XML Data Type in SQL Server 2005]
 * [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xmloptions.asp XML Options in SQL Server 2005]
 * [http://www.sqlservercentral.com/articles/XML/63889/ A Generic Process to Convert XML Data - Part 1]
 * [http://www.sqlservercentral.com/articles/XML/66932/ Split string using XML]
 * [http://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ Using the FOR XML Clause to Return Query Results as XML]
 * [http://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ Using the FOR XML Clause to Return Query Results as XML]
 * [attachment:Using_XML_In_SQL_Server_2005.pdf Using In SQL Server 2005]
 * http://www.sqlservercentral.com/Authors/Articles/Jacob_Sebastian/212008/
 * [http://www.mssqltips.com/tip.asp?tip=1748 Comparing Files from Different Folders Using SQL Server and XML]