인녕하세요 ^^
* 다음의 XML 값이 있습니다.
<root>
<tg id="0">
<ind pos="LLL">
<lgn>aa</lgn>
<rgn>bb</rgn>
</ind>
<ind pos="LLR">
<lgn>cc</lgn>
<rgn>dd</rgn>
</ind>
</tg>
<tg id="1">
<ind pos="LL">
<lgn>ee</lgn>
<rgn>ff</rgn>
</ind>
<ind pos="LR">
<lgn>gg</lgn>
<rgn>hh</rgn>
</ind>
</tg>
...
<tg id="16">
<ind pos="L">
<lgn>ww</lgn>
<rgn>xx</rgn>
</ind>
<ind pos="R">
<lgn>yy</lgn>
<rgn>zz</rgn>
</ind>
</tg>
</root>
위의 XML을 SELECT해서 아래와 같이 변환하려고 합니다.
오랜만에 XML 쿼리를 하려니 쉽지가 않네요 ㅠ. 조언 부탁드립니다.
tgid ind lgn rgn
0 LLL aa bb
0 LLR cc dd
1 LL ee ff
1 LR gg hh
...
16 L ww xx
16 R yy zz
참고하십시오.
declare @xml varchar(max) = '<root>
<tg id="0">
<ind pos="LLL">
<lgn>aa</lgn>
<rgn>bb</rgn>
</ind>
<ind pos="LLR">
<lgn>cc</lgn>
<rgn>dd</rgn>
</ind>
</tg>
<tg id="1">
<ind pos="LL">
<lgn>ee</lgn>
<rgn>ff</rgn>
</ind>
<ind pos="LR">
<lgn>gg</lgn>
<rgn>hh</rgn>
</ind>
</tg>
<tg id="16">
<ind pos="L">
<lgn>ww</lgn>
<rgn>xx</rgn>
</ind>
<ind pos="R">
<lgn>yy</lgn>
<rgn>zz</rgn>
</ind>
</tg>
</root>'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml
SELECT *
FROM OPENXML (@docHandle, '/root/tg/ind',3)
WITH (
ID INT '../@id',
POS varchar(10) '@pos',
LGN varchar(10) 'lgn',
RGN varchar(10) 'rgn'
)
EXEC sp_xml_removedocument @docHandle