Use AUTO Mode with FOR XML (SQL Server 2012) Multiple Table

1 SELECT Cust.CustomerID, 
2        OrderHeader.CustomerID,
3        OrderHeader.SalesOrderID, 
4        OrderHeader.Status
5 FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
6 WHERE Cust.CustomerID = OrderHeader.CustomerID

 

1 SELECT Cust.CustomerID, 
2        OrderHeader.CustomerID,
3        OrderHeader.SalesOrderID, 
4        OrderHeader.Status
5 FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
6 WHERE Cust.CustomerID = OrderHeader.CustomerID
7 FOR XML AUTO
<Cust CustomerID="29825">
  <OrderHeader CustomerID="29825" SalesOrderID="43659" Status="5" />
</Cust>
<Cust CustomerID="29672">
  <OrderHeader CustomerID="29672" SalesOrderID="43660" Status="5" />
</Cust>
<Cust CustomerID="29734">
  <OrderHeader CustomerID="29734" SalesOrderID="43661" Status="5" />
</Cust>
<Cust CustomerID="29994">
  <OrderHeader CustomerID="29994" SalesOrderID="43662" Status="5" />
</Cust>
<Cust CustomerID="29565">
  <OrderHeader CustomerID="29565" SalesOrderID="43663" Status="5" />
</Cust>
<Cust CustomerID="29898">
  <OrderHeader CustomerID="29898" SalesOrderID="43664" Status="5" />
</Cust>
1 select OrderHeader.CustomerID,
2        Cust.CustomerID,
3        OrderHeader.SalesOrderID,
4        OrderHeader.Status
5 from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
6 where Cust.CustomerID = OrderHeader.CustomerID
7 for xml auto
<OrderHeader CustomerID="29825" SalesOrderID="43659" Status="5">
  <Cust CustomerID="29825" />
</OrderHeader>
<OrderHeader CustomerID="29672" SalesOrderID="43660" Status="5">
  <Cust CustomerID="29672" />
</OrderHeader>
<OrderHeader CustomerID="29734" SalesOrderID="43661" Status="5">
  <Cust CustomerID="29734" />
</OrderHeader>
<OrderHeader CustomerID="29994" SalesOrderID="43662" Status="5">
  <Cust CustomerID="29994" />
</OrderHeader>
<OrderHeader CustomerID="29565" SalesOrderID="43663" Status="5">
  <Cust CustomerID="29565" />
</OrderHeader>
<OrderHeader CustomerID="29898" SalesOrderID="43664" Status="5">
  <Cust CustomerID="29898" />
</OrderHeader>

 

关于SELECT 字句:

Cust 为Sales.Customer的别名,OrderHeader 为Sales.SalesOrderHeader的别名

属性内容参考第一列所属表(包含列中的所有表),属性则参考其余表

 

posted @ 2013-09-04 11:05  Ling.Qiu.泠  阅读(244)  评论(0编辑  收藏  举报