编写生成通用表的查询的过程和对通用表行集进行处理以生成所得到的 XML 文档的过程,与在示例 A 或示例 B 中描述的过程相似。
SELECT 1 as Tag, NULL as Parent, C.CustomerID as [Customer!1!cid], C.ContactName as [Customer!1!name], NULL as [Order!2!id], NULL as [Order!2!date], NULL as [OrderDetail!3!id!id], NULL as [OrderDetail!3!pid!idref] FROM Customers C UNION ALL SELECT 2 as Tag, 1 as Parent, C.CustomerID, NULL, O.OrderID, O.OrderDate, NULL, NULL FROM Customers C, Orders O WHERE C.CustomerID = O.CustomerID UNION ALL SELECT 3 as Tag, 2 as Parent, C.CustomerID, NULL, O.OrderID, NULL, OD.OrderID, OD.ProductID FROM Customers C, Orders O, [Order Details] OD WHERE C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID ORDER BY [Customer!1!cid], [Order!2!id] FOR XML EXPLICIT
SELECT 1 as Tag,
NULL as Parent,
C.CustomerID as [Customer!1!cid],
C.ContactName as [Customer!1!name],
NULL as [Order!2!id],
NULL as [Order!2!date],
NULL as [OrderDetail!3!id!id],
NULL as [OrderDetail!3!pid!idref]
FROM Customers C
UNION ALL
SELECT 2 as Tag,
1 as Parent,
C.CustomerID,
NULL,
O.OrderID,
O.OrderDate,
NULL,
NULL
FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID
UNION ALL
SELECT 3 as Tag,
2 as Parent,
C.CustomerID,
NULL,
O.OrderID,
NULL,
OD.OrderID,
OD.ProductID
FROM Customers C, Orders O, [Order Details] OD
WHERE C.CustomerID = O.CustomerID
AND O.OrderID = OD.OrderID
ORDER BY [Customer!1!cid], [Order!2!id]
FOR XML EXPLICIT
下面是部分结果:
<Customer cid="ALFKI" name="Maria Anders">
<Order id="10643" date="1997-08-25T00:00:00">
<OrderDetail id="10643" pid="28"></OrderDetail>
<OrderDetail id="10643" pid="39"></OrderDetail>
</Order>
<Order id="10692" date="1997-10-03T00:00:00">
<OrderDetail id="10692" pid="63"></OrderDetail>
</Order>
<Order id="10702" date="1997-10-13T00:00:00">
<OrderDetail id="10702" pid="3"></OrderDetail>
<OrderDetail id="10702" pid="76"></OrderDetail>
</Order>
</Customer>
在sqlserver的帮助有的
就会以XML格式显示。