牛人的程序：将数据库数据转换为XML文件

不知道是哪位牛人的程序，放在这里怕丢了。

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>




   
   */