Example: Specifying the ID and IDREFS Directives
An element attribute can be specified as an ID type attribute, and the IDREFS attribute can then be used to refer to it. This enables intra-document links and is similar to the primary key and foreign key relationships in relational databases.
This example illustrates how the ID and IDREFS directives can be used to create attributes of ID and IDREFS types. Because IDs cannot be integer values, the ID values in this example are converted. In other words, they are type casted. Prefixes are used for the ID values.
Assume that you want to construct XML as shown in the following:
<Customer CustomerID="C1" SalesOrderIDList=" O11 O22 O33..." >
<SalesOrder SalesOrderID="O11" OrderDate="..." />
<SalesOrder SalesOrderID="O22" OrderDate="..." />
<SalesOrder SalesOrderID="O33" OrderDate="..." />
...
</Customer>
The SalesOrderIDList attribute of the < Customer > element is a multivalued attribute that refers to the SalesOrderID attribute of the < SalesOrder > element. To establish this link, the SalesOrderID attribute must be declared of ID type, and the SalesOrderIDList attribute of the < Customer> element must be declared of IDREFS type. Because a customer can request several orders, the IDREFS type is used.
Elements of IDREFS type also have more than one value. Therefore, you have to use a separate select clause that will reuse the same tag, parent, and key column information. The ORDER BY then has to ensure that the sequence of rows that make up the IDREFS values appears grouped together under their parent element.
This is the query that produces the XML you want. The query uses the ID and IDREFS directives to overwrite the types in the column names (SalesOrder!2!SalesOrderID!ID, Customer!1!SalesOrderIDList!IDREFS).
USE AdventureWorks2008R2;
GO
SELECT 1 as Tag,
0 as Parent,
C.CustomerID [Customer!1!CustomerID],
NULL [Customer!1!SalesOrderIDList!IDREFS],
NULL [SalesOrder!2!SalesOrderID!ID],
NULL [SalesOrder!2!OrderDate]
FROM Sales.Customer C
UNION ALL
SELECT 1 as Tag,
0 as Parent,
C.CustomerID,
'O-'+CAST(SalesOrderID as varchar(10)),
NULL,
NULL
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
ON C.CustomerID = SOH.CustomerID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
C.CustomerID,
NULL,
'O-'+CAST(SalesOrderID as varchar(10)),
OrderDate
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
ON C.CustomerID = SOH.CustomerIDORDER BY [Customer!1!CustomerID] ,
[SalesOrder!2!SalesOrderID!ID],
[Customer!1!SalesOrderIDList!IDREFS]
FOR XML EXPLICIT;