FOR XML 사용방법#1
DB&XML 2007. 9. 14. 16:59 |상세한 내용은 온라인에 있는 MSDN을 참고 바랍니다.
http://msdn2.microsoft.com/en-us/library/ms345137.aspx
select * from joyboard_notice for xml auto, type;
>> 결과
<joyboard_notice no="1" ... category="" />
<joyboard_notice no="2" ... category="" />
SELECT (SELECT * FROM joyboard_notice FOR XML AUTO, TYPE).query(
'<doc>{
for $c in /joyboard_notice
return
<Person name="{data($c/@id)}"/>
}</doc>')
>> 결과
<doc>
<Person name="jaeukan" />
<Person name="usarabia" />
</doc>
>> joyboard_notice : 테이블 이름
>> data($c/@id) : id는 컬럼명
SELECT ID as "@id",
Nick
FROM joyboard_notice
FOR XML PATH('Customer'), ROOT('doc')
>> 결과
<doc>
<Customer id="jaeukan">
<Nick>Azitro</Nick>
</Customer>
<Customer id="usarabia">
<Nick>사라비아</Nick>
</Customer>
</doc>
http://msdn2.microsoft.com/en-us/library/ms345137.aspx
select * from joyboard_notice for xml auto, type;
>> 결과
<joyboard_notice no="1" ... category="" />
<joyboard_notice no="2" ... category="" />
SELECT (SELECT * FROM joyboard_notice FOR XML AUTO, TYPE).query(
'<doc>{
for $c in /joyboard_notice
return
<Person name="{data($c/@id)}"/>
}</doc>')
>> 결과
<doc>
<Person name="jaeukan" />
<Person name="usarabia" />
</doc>
>> joyboard_notice : 테이블 이름
>> data($c/@id) : id는 컬럼명
SELECT ID as "@id",
Nick
FROM joyboard_notice
FOR XML PATH('Customer'), ROOT('doc')
>> 결과
<doc>
<Customer id="jaeukan">
<Nick>Azitro</Nick>
</Customer>
<Customer id="usarabia">
<Nick>사라비아</Nick>
</Customer>
</doc>