Å£È˵ijÌÐò£º½«Êý¾Ý¿âÊý¾Ýת»»ÎªXMLÎļþ

²»ÖªµÀÊÇÄÄλţÈ˵ijÌÐò£¬·ÅÔÚÕâÀïŶªÁË¡£
SQL code
CREATE TABLE tb(id int primary key,val numeric(10,2),date datetime,name varchar(100))
INSERT tb(id,val,date,name) SELECT 1,12,GETDATE(),'СÁº'
UNION ALL SELECT 12,29.334,'2010-09-12','À¼¶ù';
SELECT
CAST('<DATAPACKET Version="2.0"><METADATA>'+
(SELECT A.name AS [@attrname],B.name AS [@fieldtype],
CASE WHEN EXISTS(SELECT * from sys.indexes AS C
JOIN sys.index_columns AS D
ON C.object_id=D.object_id AND C.index_id=D.index_id
WHERE C.object_id=A.object_id AND D.column_id=A.column_id
AND C.is_primary_key=1)
THEN 'true' END AS [@IS_PRIMARY_KEY],
A.max_length AS [@WIDTH]
from sys.columns AS A
JOIN sys.types AS B
ON A.user_type_id = B.user_type_id AND object_id=OBJECT_ID('tb')
FOR XML PATH('FIELD'),ROOT('FIELDS'))+'</METADATA>'
+
(
SELECT * from tb FOR XML RAW('ROW'),ROOT('ROWDATA')
) + '</DATAPACKET>'
AS xml);
DROP TABLE tb;
XMLÎļþ£º
/*
<DATAPACKET Version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="id" fieldtype="int" IS_PRIMARY_KEY="true" WIDTH="4" />
<FIELD attrname="val" fieldtype="numeric" WIDTH="9" />
<FIELD attrname="date" fieldtype="datetime" WIDTH="8" />
<FIELD attrname="name" fieldtype="varchar" WIDTH="100" />
</FIELDS>
</METADATA>
<ROWDATA>
<ROW id="1" val="12.00" date="2010-04-21T11:27:46.687" name="СÁº" />
<ROW id="12" val="29.33" date="2010-09-12T00:00:00" name="À¼¶ù" />
</ROWDATA>
</DATAPACKET>
*/