将数据库表中数据转为XML

SQL语句如下：

View Code

 1 CREATE TABLE #CUSTOMER(
 2 ID INT NOT NULL,
 3 NAME VARCHAR(30),
 4 TELE VARCHAR(99),
 5 GENDER VARCHAR(5))
 6 
 7 CREATE TABLE #ADDRESS(
 8 CID INT NOT NULL,
 9 COUNTRY VARCHAR(30),
10 PROVINCE VARCHAR(99),
11 CITY VARCHAR(99),
12 ADDR VARCHAR(200))
13 
14 INSERT INTO #CUSTOMER
15 SELECT 1,'ZHANG','1380XXXX000','F' UNION ALL
16 SELECT 2,'WANG','021-5596XXXX','F' UNION ALL
17 SELECT 3,'MAO','135XXXX2596','M'
18 
19 
20 INSERT INTO #ADDRESS
21 SELECT 1,'PRC','SHANGHAI','SHANGHAI','浦东新区蔡伦路1号' UNION ALL
22 SELECT 1,'PRC','SHANGHAI','SHANGHAI','卢湾区淮海路1号' UNION ALL
23 SELECT 1,'PRC','SHANGHAI','SHANGHAI','徐汇区古美路88号' UNION ALL
24 SELECT 2,'PRC','JIANGSU','NANJING','中山东路305号' UNION ALL
25 SELECT 2,'PRC','JIANGSU','SUZHOU','公园路198号' UNION ALL
26 SELECT 2,'PRC','JIANGSU','WUXI','广瑞路2号（市人力资源市场）三楼大厅' UNION ALL
27 SELECT 2,'PRC','JIANGSU','YANGZHOU','文昌中路575、577号(医院总部)' UNION ALL
28 SELECT 3,'PRC','ZHEJIANG','HANGZHOU','杭州经济技术开发区白杨街道2号大街1号'
29 
30 DECLARE @XML XML
31 
32 SELECT @XML=BODY
33 FROM (SELECT 
34          (SELECT ID,NAME,TELE,GENDER,(
35               SELECT COUNTRY,PROVINCE,CITY,ADDR
36               FROM #ADDRESS WHERE #ADDRESS.CID=#CUSTOMER.ID
37               FOR XML RAW('ADDRESS'),TYPE)
38           FROM #CUSTOMER FOR XML RAW('CUSTOMER'),TYPE
39       ) FOR XML RAW('BODY'),TYPE
40       ) X (BODY) 
41 
42 SELECT @XML
43 
44 
45 DROP TABLE #CUSTOMER
46 DROP TABLE #ADDRESS

得到的XML如下：

View Code

<BODY>
  <CUSTOMER ID="1" NAME="ZHANG" TELE="1380XXXX000" GENDER="F">
    <ADDRESS COUNTRY="PRC" PROVINCE="SHANGHAI" CITY="SHANGHAI" ADDR="浦东新区蔡伦路1号" />
    <ADDRESS COUNTRY="PRC" PROVINCE="SHANGHAI" CITY="SHANGHAI" ADDR="卢湾区淮海路1号" />
    <ADDRESS COUNTRY="PRC" PROVINCE="SHANGHAI" CITY="SHANGHAI" ADDR="徐汇区古美路88号" />
  </CUSTOMER>
  <CUSTOMER ID="2" NAME="WANG" TELE="021-5596XXXX" GENDER="F">
    <ADDRESS COUNTRY="PRC" PROVINCE="JIANGSU" CITY="NANJING" ADDR="中山东路305号" />
    <ADDRESS COUNTRY="PRC" PROVINCE="JIANGSU" CITY="SUZHOU" ADDR="公园路198号" />
    <ADDRESS COUNTRY="PRC" PROVINCE="JIANGSU" CITY="WUXI" ADDR="广瑞路2号（市人力资源市场）三楼大厅" />
    <ADDRESS COUNTRY="PRC" PROVINCE="JIANGSU" CITY="YANGZHOU" ADDR="文昌中路575、577号(医院总部)" />
  </CUSTOMER>
  <CUSTOMER ID="3" NAME="MAO" TELE="135XXXX2596" GENDER="M">
    <ADDRESS COUNTRY="PRC" PROVINCE="ZHEJIANG" CITY="HANGZHOU" ADDR="杭州经济技术开发区白杨街道2号大街1号" />
  </CUSTOMER>
</BODY>