openxml - xml 데이터 처리하기

MS-SQL XML 데이터 뷰

declare @idoc int, @doc xml
set @doc = N'<?xml version="1.0"?>
<list>
<dong>
    <name_en>Mugyo-dong, Jung-gu, Seoul, Korea</name_en>
    <name_kr>서울특별시 중구 무교동</name_kr> 
    <web>http://www.무교동.com</web>
</dong>
<dong>
    <name_en>Gunja-dong, Gwangjin-gu, Seoul, Korea</name_en>
    <name_kr>서울특별시 광진구 군자동</name_kr> 
    <web>http://www.군자동.com</web>
</dong>
<dong>
    <name_en>Guui 1-dong, Gwangjin-gu, Seoul, Korea</name_en>
    <name_kr>서울특별시 광진구 구의1동</name_kr> 
    <web>http://www.구의1동.com</web>
</dong>
<dong>
    <name_en>Guui 2-dong, Gwangjin-gu, Seoul, Korea</name_en>
    <name_kr>서울특별시 광진구 구의2동</name_kr> 
    <web>http://www.구의2동.com</web>
</dong>
</list>'

exec sp_xml_preparedocument @idoc OUTPUT, @doc
select *
from openxml (@idoc, '/list/dong', 1)
with (
engName nvarchar(100) 'name_en',
korName nvarchar(100) 'name_kr',
url nvarchar(200) 'web'
)  A
 
exec  sp_xml_removedocument @idoc