Skip to content

example-specifying-the-id-and-idrefs-directives.md

Latest commit

 

History

History
84 lines (70 loc) · 3.66 KB

example-specifying-the-id-and-idrefs-directives.md

File metadata and controls

84 lines (70 loc) · 3.66 KB
title Example: Specifying the ID and IDREFS Directives | Microsoft Docs
description Learn how specifying the ID and IDREFS directives in an SQL query can enable intra-document links.
ms.custom fresh2019may
ms.date 05/22/2019
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology xml
ms.topic conceptual
helpviewer_keywords
IDREFS directive
ID directive
ms.assetid 99b9f0d8-ecbb-4225-859f-881066c09785
author MightyPen
ms.author genemi

Example: Specifying the ID and IDREFS Directives

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

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 AdventureWorks2012;  
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.CustomerID
ORDER BY [Customer!1!CustomerID] ,
         [SalesOrder!2!SalesOrderID!ID],  
         [Customer!1!SalesOrderIDList!IDREFS]  
FOR XML EXPLICIT;

See Also

Use EXPLICIT Mode with FOR XML