SQL Server2005中使用XML－FOR XML(RAW模式查询)

来源：互联网发布：电路图绘制软件有哪些编辑：程序博客网时间：2024/05/18 02:55

ROW模式将查询结果中的每一行转换为带有通用标识符<row>（或提供的元素名）的XML元素。
1、以通用Row元素检索数据
select Cust.CustomerID CustID,CustomerType,SalesOrderID from Sales.Customer Custjoin Sales.SalesOrderHeader [order]on Cust.CustomerID=[order].CustomerIDorder by Cust.CustomerID for XML RAW
结果如下：
<row CustID="1" CustomerType="S" SalesOrderID="43860" /><row CustID="1" CustomerType="S" SalesOrderID="44501" /><row CustID="1" CustomerType="S" SalesOrderID="45283" /><row CustID="1" CustomerType="S" SalesOrderID="46042" />

2、将数据作为元素检索
select Cust.CustomerID CustID,CustomerType,SalesOrderID from Sales.Customer Custjoin Sales.SalesOrderHeader [order]on Cust.CustomerID=[order].CustomerIDorder by Cust.CustomerID for XML RAW,ELEMENTS
结果如下：
<row> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>43860</SalesOrderID></row><row> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>44501</SalesOrderID></row><row> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>45283</SalesOrderID></row>

3、使用根元素和自定义的行元素名检索数据
select Cust.CustomerID CustID,CustomerType,SalesOrderID from Sales.Customer Custjoin Sales.SalesOrderHeader [order]on Cust.CustomerID=[order].CustomerIDorder by Cust.CustomerID for XML RAW('Order'),Root('Orders')
结果如下：
<Orders> <Order CustID="1" CustomerType="S" SalesOrderID="43860" /> <Order CustID="1" CustomerType="S" SalesOrderID="44501" /> <Order CustID="1" CustomerType="S" SalesOrderID="45283" /> <Order CustID="1" CustomerType="S" SalesOrderID="46042" /></Orders>
同时指定ELEMENTS选项：
select Cust.CustomerID CustID,CustomerType,SalesOrderID from Sales.Customer Custjoin Sales.SalesOrderHeader [order]on Cust.CustomerID=[order].CustomerIDorder by Cust.CustomerID for XML RAW('Order'),Root('Orders'),ELEMENTS
结果如下：
<Orders> <Order> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>43860</SalesOrderID> </Order> <Order> <CustID>1</CustID> <CustomerType>S</CustomerType> <SalesOrderID>44501</SalesOrderID> </Order></Orders>