SQL XML : how to use “FOR XML PATH”

“FOR XML PATH” can give almost similar output what “FOR XML EXPLICIT” can give.

Input:

DECLARE @t TABLE ( yourData VARCHAR(10) PRIMARY KEY )
DECLARE @xml XML

INSERT INTO @t
VALUES ( 'A' ), ( 'B' ), ( 'C' )

SELECT
	'arch' AS "@name",
	'xml' AS "@type",
	(
	SELECT
		'bottom' AS "@type",
		'Quá Trình Lương' AS "@string",
		(
		SELECT yourData AS "@name"
		FROM @t
		FOR XML PATH('field'), TYPE
		)
	FOR XML PATH('tree'), TYPE
	)
FOR XML PATH('field'), TYPE

Output:

<field name="arch" type="xml">
 <tree type="bottom" string="Quá Trình Luong">
    <field name="A" />
    <field name="B" />
    <field name="C" />
 </tree>
</field>

Reff Source: http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/2a77e61a-38c6-406c-83b6-1e99c40dfd4f

Advertisements