在SQL Server将表格类型数据转换成XML格式的数据,只需要在常规SQL语句中附加FOR XML<mode>子句。
1. FOR XML RAW :可以获得一个以属性为中心的XML数据视图,每一行包裹在一个<row>元素中。但没有加入document元素,因此它仅是一个XML片段。
例如:
SELECT [PurchaseOrderID]
,[RevisionNumber]
,[Status]
,[EmployeeID]
,[VendorID]
,[ShipMethodID]
,[OrderDate]
,[ShipDate]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[ModifiedDate]
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [TotalDue] > 300000
FOR XML RAW;
查询返回的结果集为:
<row PurchaseOrderID="4007" RevisionNumber="10" Status="2" EmployeeID="164" VendorID="102" ShipMethodID="3" OrderDate="2004-04-01T00:00:00" ShipDate="2004-04-26T00:00:00" SubTotal="554020.0000" TaxAmt="44321.6000" Freight="11080.4000" TotalDue="609422.0000" ModifiedDate="2005-09-12T12:25:46.407" />
<row PurchaseOrderID="4008" RevisionNumber="16" Status="2" EmployeeID="244" VendorID="95" ShipMethodID="3" OrderDate="2004-05-23T00:00:00" ShipDate="2004-06-17T00:00:00" SubTotal="396729.0000" TaxAmt="31738.3200" Freight="7934.5800" TotalDue="436401.9000" ModifiedDate="2005-09-12T12:25:46.420" />
<row PurchaseOrderID="4012" RevisionNumber="5" Status="2" EmployeeID="231" VendorID="29" ShipMethodID="3" OrderDate="2004-07-25T00:00:00" ShipDate="2004-08-19T00:00:00" SubTotal="997680.0000" TaxAmt="79814.4000" Freight="19953.6000" TotalDue="1097448.0000" ModifiedDate="2005-09-12T12:25:46.483" />
FOR XML RAW,ELEMENTS : 返回是以元素为中心的视图。
例如:
SELECT [PurchaseOrderID]
,[RevisionNumber]
,[Status]
,[EmployeeID]
,[VendorID]
,[ShipMethodID]
,[OrderDate]
,[ShipDate]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[ModifiedDate]
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [TotalDue] > 300000
FOR XML RAW, ELEMENTS;
查询返回的结果集为:
<row>
<PurchaseOrderID>4007</PurchaseOrderID>
<RevisionNumber>10</RevisionNumber>
<Status>2</Status>
<EmployeeID>164</EmployeeID>
<VendorID>102</VendorID>
<ShipMethodID>3</ShipMethodID>
<OrderDate>2004-04-01T00:00:00</OrderDate>
<ShipDate>2004-04-26T00:00:00</ShipDate>
<SubTotal>554020.0000</SubTotal>
<TaxAmt>44321.6000</TaxAmt>
<Freight>11080.4000</Freight>
<TotalDue>609422.0000</TotalDue>
<ModifiedDate>2005-09-12T12:25:46.407</ModifiedDate>
</row>
FOR XML RAW, ELEMENTS, ROOT('orders') : 为文档添加一个根元素,使用ROOT指明根元素名称。
例如:
SELECT [PurchaseOrderID]
,[RevisionNumber]
,[Status]
,[EmployeeID]
,[VendorID]
,[ShipMethodID]
,[OrderDate]
,[ShipDate]
,[SubTotal]
,[TaxAmt]
,[Freight]
,[TotalDue]
,[ModifiedDate]
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [TotalDue] > 300000
FOR XML RAW, ELEMENTS, ROOT('orders');
查询返回的结果集为:
<orders>
<row>
<PurchaseOrderID>4007</PurchaseOrderID>
<RevisionNumber>10</RevisionNumber>
<Status>2</Status>
<EmployeeID>164</EmployeeID>
<VendorID>102</VendorID>
<ShipMethodID>3</ShipMethodID>