xml 문서를 만들거나 이미 만들어진 xml문서를 읽어 올 경우 쿼리로 작업이 가능합니다
이 경우 Select내용을 xml로 만들거나 만들어진 xml을 테이블 결과로 불러오는 방법이 있는데 for xml explicit과 for xml auto를 이용하여 xml문서로 select결과를 만들수 있으며 다음과 같습니다
Using - for xml explicit
-- Make xml message
declare @msg nvarchar(max) = '';
set @msg = @msg + '<mgp title="player_info">';
set @msg = @msg + convert(nvarchar(max),
(
select 1 as tag
, null as parent
, player_id as [player!1!player_id]
, player_name as [player!1!player_name]
, nickname as [player!1!nickname]
, back_no as [player!1!back_no]
from dbo.player with(nolock)
where player_id in (2000001, 2000002, 2000003)
for xml explicit
));
set @msg = @msg + convert(nvarchar(max),
(
select 2 as tag
, null as parent
, player_id as [coach!2!player_id]
, player_name as [coach!2!player_name]
, nickname as [coach!2!nickname]
, back_no as [coach!2!back_no]
from dbo.player with(nolock)
where player_id in (2000031, 2000032, 2000033)
for xml explicit
));
set @msg = @msg + '</mgp>';
위의 경우는 데이터 테이블의 결과를 xml로 만드는 과정입니다. select문에 from xml explicit를 추가함으로써 테이블 형식의 쿼리결과를 xml형태의 문서로 변환할 수 있습니다
xml로 변환시 tag와 parent부분을 꼭 써줘야 하는데 해당영역은 Select요소에 대한 고유 Tag번호 이며, Parent를 이용하여 xml계층구조를 만들 수 있게되는 값입니다
xml로 변환시에 Select절에 다음과 같은 형식으로 써야합니다
xml속성값 명칭 as [태그명!Tag번호!Table에서 선택할 칼럼명]
쿼리의 결과를 <mgp>~</mgp>로 묶은 이유는 xml문서에서 depth를 만들기 위함이며 다음과 같은 결과로 받아올 수 있습니다
<mgp title="player_info">
<player player_id="2000001" player_name="김태호" nickname="" back_no="0" />
<player player_id="2000002" player_name="정상수" nickname="" back_no="0" />
<player player_id="2000003" player_name="유동우" nickname="" back_no="40" />
<coach player_id="2000031" player_name="차경복" nickname="" back_no="0" />
<coach player_id="2000032" player_name="정학범" nickname="" back_no="0" />
<coach player_id="2000033" player_name="안익수" nickname="" back_no="0" />
</mgp>
위의 내용은 @msg 변수를 Select했을시 값입니다. mgp로 xml depth를 나눴는데 쿼리의 결과를 단위별로 묶으면 불러올 때 어떤내용인지 관리하기 편리합니다
아래의 내용은 위에서 만든 @msg변수를 다시 Select절로 불러오려고 할 때의 방법입니다
sp_xml_preparedocument라는 DBMS내장 도움말 함수를 이용하여 xml을 불러올수 있으며 @h_xml값은 @msg(xml형식)를 고유의 핸들링 할수있는 정수값으로 변환합니다
즉, @h_xml의 정수값은 @msg와 같은 것이라고 볼 수 있습니다
-- Open xml message
declare @h_xml int = 0
exec sp_xml_preparedocument @h_xml output, @msg
select title
from openxml(@h_xml, '/mgp', 1)
with
(
title varchar(20)
)
select player_id
, player_name
, nickname
, back_no
from openxml(@h_xml, '/mgp/player', 1)
with
(
player_id char(7)
, player_name varchar(20)
, nickname varchar(30)
, back_no tinyint
)
select player_id
, player_name
, nickname
, back_no
from openxml(@h_xml, '/mgp/coach', 1)
with
(
player_id char(7)
, player_name varchar(20)
, nickname varchar(30)
, back_no tinyint
)
xml문서를 열 때 어느영역까지 열 것 인지 경로로 판단하여 값을 가져옵니다
'/mgp'까지의 경로에는 title항목 밖에 없기 때문에 title값만 가져올 수 있습니다
'/mgp/player'로 경로를 설정시에는 mgp하위의 player태그의 값들만 가져와서 select 할수 있으며 '/mgp/coach'로 경로를 설정시에는 mgp하위의 coach태그의 값들만 가져올 수 있습니다
Using - for xml explicit 계층구조 참고 스크립트
for xml explicit를 이용하여 xml계층구조를 이용할 경우에는 아래의 스크립트를 참고하시면 됩니다
Execute Script
SELECT
1 AS Tag,
NULL AS Parent,
'2.17' AS [Root!1!Version],
'XX-DD 389' AS [Root!1!KFZ],
NULL AS [Head!2!name!Element],
NULL AS [Key!3!name],
NULL AS [Key!3!value],
NULL AS [Key!3!!CDATA],
NULL AS [Section!4!name],
NULL AS [Key!5!name],
NULL AS [Key!5!value],
NULL AS [Key!6!name],
NULL AS [Key!6!!CDATA]
UNION ALL
SELECT
2 as Tag,
1 as Parent,
NULL,
NULL,
'ExecuteAnswer',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
3 as Tag,
2 as Parent,
NULL,
NULL,
NULL,
'AnswerStatus',
'-OK',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
3 as Tag,
2 as Parent,
NULL,
NULL,
NULL,
'MsgId',
NULL,
'KFHB0907896aAUH223',
NULL,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
4 as Tag,
2 as Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Command',
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
5 as Tag,
4 as Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Name',
'SetNewCommand',
NULL,
NULL
UNION ALL
SELECT
6 as Tag,
4 as Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'Param0',
'XX-DD 389'
FOR XML EXPLICIT
Result Xml
<Root Version="2.17" KFZ="XX-DD 389">
<Head>
<name>ExecuteAnswer</name>
<Key name="AnswerStatus" value="-OK" />
<Key name="MsgId"><![CDATA[KFHB0907896aAUH223]]></Key>
<Section name="Command">
<Key name="Name" value="SetNewCommand" />
<Key name="Param0"><![CDATA[XX-DD 389]]></Key>
</Section>
</Head>
</Root>
Using - for xml auto
위의 방법은 xml계층구조를 설정하고, Select절에 xml을 만들기 위한 세부사항을 입력 하여야 만들 수 있었는데 for xml auto를 사용하면 더 간단하게 xml문서로 변환할 수 있게됩니다
-- Make xml message
declare @msg2 nvarchar(max) = '';
set @msg2 = @msg2 + '<mgp>';
set @msg2 = @msg2 + convert(nvarchar(max),
(
select player_id
, player_name
, nickname
, back_no
from dbo.player with(nolock)
where player_id in (2000001, 2000002, 2000003)
for xml auto, type
)
)
set @msg2 = @msg2 + '</mgp>';
쿼리의 where절 하단에 for xml auto를 넣음으로써 쿼리의 결과를 xml로 바로 만들 수 있습니다. type을 사용하면 타입이 xml로 변환되지만 type을 사용하지 않으면 nvarchar(max)로 변환됩니다
위의 쿼리 결과인 xml을 표현하자면 다음과 같이 결과가 나옵니다
<mgp>
<dbo.player player_id="2000001" player_name="김태호" nickname="" back_no="0"/>
<dbo.player player_id="2000002" player_name="정상수" nickname="" back_no="0"/>
<dbo.player player_id="2000003" player_name="유동우" nickname="" back_no="40"/>
</mgp>
for xml auto, type 되어있는 부분을 for xml auto, elements로 하게 되면 요소중심으로 xml결과값이 나와서 더 깊은 계층구조를 만들어 내며, 다음과 같은 결과값이 나오게 됩니다
<mgp>
<dbo.player>
<player_id>2000001</player_id>
<player_name>김태호</player_name>
<nickname></nickname>
<back_no>0</back_no>
</dbo.player>
<dbo.player>
<player_id>2000002</player_id>
<player_name>정상수</player_name>
<nickname></nickname>
<back_no>0</back_no>
</dbo.player><dbo.player>
<player_id>2000003</player_id>
<player_name>유동우</player_name>
<nickname></nickname>
<back_no>40</back_no>
</dbo.player>
</mgp>
xml변환시 다음과 같은 방법으로 xml결과를 테이블 형식으로 변환할 수 있습니다
declare @h_xml2 int = 0;
exec sp_xml_preparedocument @h_xml2 output, @msg2
select @msg2
select player_id
, player_name
, nickname
, back_no
from openxml(@h_xml2, '/mgp/dbo.player', 1)
with
(
player_id char(7)
, player_name varchar(20)
, nickname varchar(30)
, back_no tinyint
)
for xml explicit을 사용할 때와 for xml auto를 사용할 때의 분명한 차이는 있으나 계층구조를 단순하게 만들경우에는 사용방식에 큰 차이가 없게됩니다. xml을 이용한 작업을 할 때 필요에 따라 방법을 가져다 쓰면 유용합니다
for xml explicit : https://docs.microsoft.com/ko-kr/sql/relational-databases/xml/use-explicit-mode-with-for-xml?view=sql-server-ver15
for xml auto : https://docs.microsoft.com/ko-kr/sql/relational-databases/xml/use-auto-mode-with-for-xml?view=sql-server-ver15
for xml auto, type : https://docs.microsoft.com/ko-kr/sql/relational-databases/xml/type-directive-in-for-xml-queries?view=sql-server-ver15