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

来源：互联网发布：kenzo 知乎编辑：程序博客网时间：2024/04/28 18:53

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

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>*/