Partager via

Utilisation du mode EXPLICIT

  • Article

Comme indiqué dans la rubrique traitant de la Construction de code XML à l'aide de FOR XML, les modes RAW et AUTO ne permettent pas de contrôler de façon précise la forme du document XML généré à partir du résultat d'une requête. Toutefois, le mode EXPLICIT offre la plus grande souplesse pour générer le document XML à partir du résultat d'une requête.

La requête en mode EXPLICIT doit être écrite de telle sorte que les informations supplémentaires sur le document XML, telles que l'imbrication attendue dans celui-ci, soient explicitement spécifiées comme une partie de la requête. Suivant le document XML demandé, l'écriture de requêtes en mode EXPLICIT peut s'avérer peu pratique. L'utilisation du mode PATH avec imbrication peut vous apparaître comme une solution plus simple pour écrire des requêtes en mode EXPLICIT.

Étant donné que vous décrivez en mode EXPLICIT le document XML souhaité dans le cadre de la requête, vous devez veiller à ce que sa forme générée soit correcte et valide.

Traitement de l'ensemble de lignes en mode EXPLICIT

Le mode EXPLICIT transforme l'ensemble de lignes issu de l'exécution de la requête en un document XML. Pour cela, l'ensemble de lignes doit être d'un format spécifique. Cela suppose que vous écriviez la requête SELECT qui génère l'ensemble de lignes, la table universelle, dans un format spécifique afin que la logique de traitement puisse produire le document XML souhaité.

Dans un premier temps, la requête doit générer les deux colonnes de métadonnées suivantes :

  • La première colonne doit fournir le numéro de balise, de type entier, de l'élément actuel et le nom de la colonne doit être Tag. La requête doit fournir un numéro de balise unique pour chaque élément à construire à partir de l'ensemble de lignes.
  • La seconde colonne doit fournir un numéro de balise de l'élément parent et porter le nom Parent. Ainsi, les colonnes Tag et Parent fournissent les informations de hiérarchie.

Ces valeurs de colonnes de métadonnées, associées aux informations des noms de colonnes, permettent de générer le document XML souhaité. La requête doit fournir les noms de colonnes d'une manière spécifique. En outre, la valeur 0 ou NULL dans la colonne Parent indique que l'élément correspondant n'a pas de parent. L'élément est ajouté au document XML en tant qu'élément de niveau supérieur.

Pour comprendre comment la table universelle générée par une requête est traitée en vue de produire un document XML, supposons que vous ayez écrit une requête qui génère la table universelle suivante :

Exemple de table universelle

Notez les points suivants à propos de cette table universelle :

  • Les deux premières colonnes sont Tag et Parent et sont des colonnes de métadonnées. Ces valeurs déterminent la hiérarchie.
  • Les noms de colonnes sont spécifiés d'une certaine manière, décrite plus loin dans cette rubrique.
  • Lors de la génération du document XML à partir de cette table universelle, les données de celle-ci sont partitionnées verticalement en groupes de colonnes. Le regroupement est déterminé en fonction de la valeur Tag et des noms de colonnes. Lors de la construction du document XML, la logique de traitement sélectionne un groupe de colonnes par ligne et construit un élément. Cet exemple repose sur les données suivantes :
    • Pour la valeur de colonne Tag 1 de la première ligne, les colonnes dont le nom comprend le même numéro de balise, Customer!1!cid et Customer!1!name, forment un groupe. Ces colonnes sont utilisées dans le traitement de la ligne et vous avez peut-être remarqué que l'élément généré présente la forme <Customer id=... name=...>. Le format des noms de colonnes est décrit plus loin dans cette rubrique.
    • Pour les lignes possédant la valeur de colonne Tag 2, les colonnes Order!2!id et Order!2!date forment un groupe qui permet de construire les éléments <Order id=... date=... />.
    • Pour les lignes possédant la valeur de colonne Tag 3, les colonnes OrderDetail!3!id!id et OrderDetail!3!pid!idref forment un groupe. Chacune de ces lignes génère un élément, <OrderDetail id=... pid=...>, à partir de ces colonnes.
  • Lors de la génération de la hiérarchie XML, les lignes sont traitées dans l'ordre. La hiérarchie XML est déterminée comme suit :

    • La première ligne spécifie la valeur Tag 1 et la valeur Parent NULL. Par conséquent, l'élément correspondant, <Customer>, est ajouté en tant qu'élément de niveau supérieur au document XML.

      <Customer cid="C1" name="Janine">

    • La deuxième ligne identifie la valeur Tag 2 et la valeur Parent 1. Par conséquent, l'élément, <Order>, est ajouté en tant qu'enfant de l'élément <Customer>.

      <Customer cid="C1" name="Janine">
   <Order id="O1" date="1/20/1996">

    • Les deux lignes suivantes identifient la valeur Tag 3 et la valeur Parent 2. Par conséquent, les deux éléments, qui sont des éléments <OrderDetail>, sont ajoutés en tant qu'enfants de l'élément <Order>.

      <Customer cid="C1" name="Janine">
   <Order id="O1" date="1/20/1996">
      <OrderDetail id="OD1" pid="P1"/>
      <OrderDetail id="OD2" pid="P2"/>

    • La dernière ligne identifie la valeur 2 comme numéro Tag et la valeur 1 comme numéro de balise Parent. Par conséquent, un autre élément enfant <Order> est ajouté à l'élément parent <Customer>.

      <Customer cid="C1" name="Janine">
   <Order id="O1" date="1/20/1996">
      <OrderDetail id="OD1" pid="P1"/>
      <OrderDetail id="OD2" pid="P2"/>
   </Order>
   <Order id="O2" date="3/29/1997">
</Customer>

En résumé, les valeurs des colonnes de métadonnées Tag et Parent, les informations fournies dans les noms de colonnes et le classement correct des lignes génèrent le document XML souhaité lorsque vous utilisez le mode EXPLICIT.

Classement des lignes de la table universelle

Lors de la construction du document XML, les lignes de la table universelle sont traitées dans l'ordre. Par conséquent, pour extraire les instances enfants adéquates associées à leur parent, vous devez trier les lignes de l'ensemble de lignes de manière à ce que chaque nœud parent soit immédiatement suivi de ses enfants.

Spécification de noms de colonnes dans une table universelle

Lors de l'écriture des requêtes en mode EXPLICIT, les noms de colonnes de l'ensemble de lignes obtenu doivent être spécifiés à l'aide du format ci-après. Ils fournissent des informations de transformation, dont les noms d'éléments et d'attributs, spécifiées à l'aide de directives.

Voici le format général :

ElementName!TagNumber!AttributeName!Directive

Voici la description des parties du format :

  • ElementName
    Identificateur générique obtenu de l'élément. Par exemple, si Customers est spécifié en tant que ElementName, l'élément <Customers> est généré.
  • TagNumber
    Valeur de balise unique affectée à un élément. Cette valeur, associée aux deux colonnes de métadonnées Tag et Parent, détermine l'imbrication des éléments dans le document XML obtenu.

  • AttributeName
    Fournit le nom de l'attribut à construire dans l'option ElementName spécifiée. Ce comportement est appliqué si l'option Directive n'est pas spécifiée.

    Si l'option Directive est spécifiée et que sa valeur est xml, cdata ou element, cette valeur est utilisée pour construire un élément enfant de ElementName et la valeur de la colonne est ajoutée à cet élément.

    Si vous spécifiez l'option Directive, l'option AttributeName peut être vide. Par exemple, ElementName!TagNumber!!Directive. Dans ce cas, la valeur de la colonne est directement contenue dans l'option ElementName.

  • Directive
    L'option Directive est facultative et permet de fournir des informations supplémentaires pour la construction du document XML. La finalité de Directive est double.

    D'une part, elle permet de coder les valeurs sous la forme de mots clés ID, IDREF et IDREFS. Vous pouvez spécifier les mots clés ID, IDREF et IDREFS en tant que Directives. Ces directives remplacent les types d'attributs. Cela vous permet de créer des liens à l'intérieur du document.

    D'autre part, l'option Directive vous permet d'indiquer comment vous souhaitez mapper les données de chaîne au document XML. Les mots clés hide, element, elementxsinil, xml, xmltext et cdata peuvent être utilisés comme Directive. La directive hide masque le nœud. Cela peut s'avérer utile pour extraire des valeurs à des fins de tri sans qu'elles apparaissent dans le document XML obtenu.

    La directive element génère un élément contenu au lieu d'un attribut. Les données contenues sont codées comme une entité. Par exemple, le caractère < devient &lt;. Dans le cas des valeurs de colonne NULL, aucun élément n'est généré. Si vous souhaitez qu'un élément soit généré pour les valeurs de colonne NULL, vous pouvez spécifier la directive elementxsinil. Cette opération génère un élément ayant l'attribut xsi:nil=TRUE.

    La directive xml est identique à une directive element, sauf qu'aucun codage d'entité ne se produit. La directive element peut être combinée avec le mot clé ID, IDREF ou IDREFS, tandis que la directive xml n'est autorisée avec aucune autre directive, sauf hide.

    La directive cdata englobe les données à l'aide d'une section CDATA. Le contenu n'est pas codé par entité. Le type de données d'origine doit être un type texte tel que varchar, nvarchar, text ou ntext. Cette directive ne peut être utilisée qu'avec hide. Quand cette directive est utilisée, AttributeName ne doit pas être spécifié.

    La combinaison de directives entre ces deux groupes est autorisée dans la plupart des cas, contrairement à la combinaison entre elles-mêmes.

    Si les options Directive et AttributeName ne sont pas spécifiées, par exemple Customer!1, une directive element est définie de manière implicite, telle que Customer!1!!element, et les données de colonnes figurent dans l'option ElementName.

    Si la directive xmltext est spécifiée, le contenu des colonnes est englobé dans une balise unique intégrée au reste du document. Cette directive est utile pour extraire des données XML non consommées, en excès, stockées dans une colonne à l'aide de OPENXML. Pour plus d'informations, consultez Interrogation de données XML à l'aide de OPENXML.

    Si l'option AttributeName est définie, le nom de balise est remplacé par le nom spécifié. Sinon, l'attribut est ajouté à la liste actuelle des attributs des éléments d'encadrement par placement du contenu au début de l'imbrication sans codage d'entité. La colonne à laquelle cette directive est associée doit être d'un type texte, tel que varchar, nvarchar, char, nchar, text ou ntext. Cette directive ne peut être utilisée qu'avec hide. Elle est utile pour extraire des données en excès stockées dans une colonne. Si le contenu ne présente pas un format XML correct, le comportement n'est pas défini.

Exemples

Les exemples ci-après illustrent l'utilisation du mode EXPLICIT.

A. Extraction d'informations sur les employés

Cet exemple extrait l'ID et le nom de chaque employé. Dans la base de données AdventureWorks, les ID des employés se trouvent dans la table Employee. Les noms des employés figurent dans la table Contact. La colonne ContactID peut être utilisée pour joindre les tables.

Supposons que vous souhaitiez générer un document XML à l'aide de la transformation FOR XML EXPLICIT comme suit :

<Employee EmpID="1" >
  <Name FName="Guy" LName="Gilbert" />
</Employee>
...

Étant donné que la hiérarchie comprend deux niveaux, vous écrivez deux requêtes SELECT et appliquez UNION ALL. Voici la première requête qui extrait les valeurs de l'élément <Employee> et de ses attributs. La requête attribue 1 comme valeur Tag pour l'élément <Employee> et NULL comme valeur Parent, car il s'agit de l'élément de niveau supérieur.

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID

Voici la seconde requête. Elle extrait les valeurs de l'élément <Name>. Elle attribue 2 comme valeur Tag pour l'élément <Name> et 1 comme valeur de balise Parent, identifiant <Employee> en tant que parent.

SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID

Vous combinez ces requêtes avec UNION ALL, appliquez FOR XML EXPLICIT et spécifiez la clause ORDER BY requise. Vous devez trier l'ensemble de lignes par EmployeeID puis par nom afin que les valeurs NULL du nom apparaissent en premier. En exécutant la requête suivante sans la clause FOR XML, vous pouvez visualiser la table universelle générée.

Voici la requête finale :

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT

Voici le résultat partiel :

<Employee EmpID="1">
  <Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
  <Name FName="Kevin" LName="Brown" />
</Employee>
...

La première instruction SELECT spécifie les noms des colonnes dans l'ensemble de lignes obtenu. Ces noms forment deux groupes de colonnes. Le groupe qui possède la valeur Tag 1 dans le nom de colonne identifie Employee en tant qu'élément et EmpID en tant qu'attribut. L'autre groupe de colonnes possède la valeur Tag 2 dans la colonne et identifie <Name> en tant qu'élément et FName et LName en tant qu'attributs.

Le tableau suivant montre l'ensemble de lignes partiel généré par la requête :

Tag Parent  Employee!1!EmpID Name!2!FName Name!2!LName
----------- ----------- ---------------- -------------------
1    NULL     1                NULL          NULL
2     1       1                Guy           Gilbert
1    NULL     2                NULL          NULL
2     1       2                Kevin         Brown
1    NULL     3                NULL          NULL
2     1       3                Roberto       Tamburello 
...

Les lignes de la table universelle sont traitées comme suit pour générer l'arborescence XML obtenue :

La première ligne identifie la valeur Tag 1. Par conséquent, le groupe de colonnes qui possède la valeur Tag 1 est identifié, en l'occurrence Employee!1!EmpID. Cette colonne identifie Employee en tant que nom d'élément. Un élément <Employee> possédant les attributs EmpID est ensuite créé. Les valeurs de colonnes correspondantes sont affectées à ces attributs.

La deuxième ligne possède la valeur Tag 2. Par conséquent, le groupe de colonnes qui possède la valeur Tag 2 dans le nom de colonne, Name!2!FName, Name!2!LName, est identifié. Ces noms de colonnes identifient Name comme nom d'élément. Un élément <Name> possédant les attributs FName et LName est créé. Les valeurs de colonnes correspondantes sont ensuite affectées à ces attributs. Cette ligne identifies 1 en tant que Parent. Cet élément enfant est ajouté à l'élément <Employee> précédent.

Ce processus est répété pour le reste des lignes de l'ensemble de lignes. Notez l'importance du tri des lignes dans la table universelle pour que FOR XML EXPLICIT puisse traiter l'ensemble de lignes dans l'ordre et générer le document XML souhaité.

B. Spécification de la directive d'un élément

Cet exemple est similaire à l'exemple A, sauf qu'il génère un document XML centré sur l'élément, comme le montre la syntaxe suivante :

<Employee EmpID=...>
  <Name>
    <FName>...</FName>
    <LName>...</LName>
  </Name>
</Employee>

La requête demeure la même, à l'exception du fait que vous ajoutez la directive ELEMENT dans les noms de colonnes. Par conséquent, au lieu d'attributs, les éléments enfants <FName> et <LName> sont ajoutés à l'élément <Name>. Étant donné que la colonne Employee!1!EmpID ne spécifie pas la directive ELEMENT, EmpID est ajouté en tant qu'attribut de l'élément <Employee>.

SELECT 1 as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName!ELEMENT],
       NULL       as [Name!2!LName!ELEMENT]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName!ELEMENT]
FOR XML EXPLICIT

Le résultat partiel est le suivant.

<Employee EmpID="1">
  <Name>
    <FName>Guy</FName>
    <LName>Gilbert</LName>
  </Name>
</Employee>
<Employee EmpID="2">
  <Name>
    <FName>Kevin</FName>
    <LName>Brown</LName>
  </Name>
</Employee>
...

C. Spécification de la directive elementxsinil

Lorsque vous spécifiez la directive ELEMENT pour extraire un document XML centré sur l'élément et que la colonne possède une valeur NULL, l'élément correspondant n'est pas généré par le mode EXPLICIT. Vous pouvez éventuellement spécifier la directive ELEMENTXSINIL pour demander l'élément généré dans le cas des valeurs NULL dont l'attribut xsi:nil a pour valeur TRUE.

La requête suivante construit le document XML qui comprend une adresse d'employé. Pour les colonnes AddressLine2 et City, les noms de colonnes spécifient la directive ELEMENTXSINIL. Celle-ci génère l'élément pour les valeurs NULL des colonnes AddressLine2 et City de l'ensemble de lignes.

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID  as [Employee!1!EmpID],
       E.AddressID as [Employee!1!AddressID],
       NULL        as [Address!2!AddressID],
       NULL        as [Address!2!AddressLine1!ELEMENT],
       NULL        as [Address!2!AddressLine2!ELEMENTXSINIL],
       NULL        as [Address!2!City!ELEMENTXSINIL]
FROM   HumanResources.EmployeeAddress E, Person.Address A
WHERE  E.ContactID = A.ContactID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       E.AddressID,
       A.AddressID,
       AddressLine1, 
       AddressLine2,
       City 
FROM   HumanResources.EmployeeAddress E, Person.Address A
WHERE  E.AddressID = A.AddressID
ORDER BY [Employee!1!EmpID],[Address!2!AddressID]
FOR XML EXPLICIT

Voici le résultat partiel :

<Employee xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        EmpID="1" AddressID="61">
  <Address AddressID="61">
    <AddressLine1>7726 Driftwood Drive</AddressLine1>
    <AddressLine2 xsi:nil="true" />
    <City>Monroe</City>
  </Address>
</Employee>
...

D. Construction de frères à l'aide du mode EXPLICIT

Supposons que vous souhaitiez construire un document XML qui fournit des informations sur les commandes. Les éléments <SalesPerson> et <OrderDetail> sont frères. Chaque commande possède un élément <OrderHeader>, un élément <SalesPerson> ainsi qu'un ou plusieurs éléments <OrderDetail>.

<OrderHeader SalesOrderID=... OrderDate=... CustomerID=... >
  <SalesPerson SalesPersonID=... />
  <OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=... />
  <OrderDetail SalesOrderID=... LineTotal=... ProductID=... OrderQty=.../>
      ...
</OrderHeader>
<OrderHeader ...</OrderHeader>

La requête en mode EXPLICIT suivante construit ce document XML. La requête spécifie les valeurs Tag 1 pour l'élément <OrderHeader>, 2 pour l'élément <SalesPerson> et 3 pour l'élément <OrderDetail>. Étant donné que <SalesPerson> et <OrderDetail> sont frères, la requête spécifie la même valeur de balise Parent, 1, pour identifier l'élément <OrderHeader>.

SELECT  1 as Tag,
        0 as Parent,
        SalesOrderID  as [OrderHeader!1!SalesOrderID],
        OrderDate     as [OrderHeader!1!OrderDate],
        CustomerID    as [OrderHeader!1!CustomerID],
        NULL          as [SalesPerson!2!SalesPersonID],
        NULL          as [OrderDetail!3!SalesOrderID],
        NULL          as [OrderDetail!3!LineTotal],
        NULL          as [OrderDetail!3!ProductID],
        NULL          as [OrderDetail!3!OrderQty]
FROM   Sales.SalesOrderHeader
WHERE     SalesOrderID=43659 or SalesOrderID=43661
UNION ALL 
SELECT 2 as Tag,
       1 as Parent,
        SalesOrderID,
        NULL,
        NULL,
        SalesPersonID,  
        NULL,         
        NULL,         
        NULL,
        NULL         
FROM   Sales.SalesOrderHeader
WHERE     SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 3 as Tag,
       1 as Parent,
        SOD.SalesOrderID,
        NULL,
        NULL,
        SalesPersonID,
        SOH.SalesOrderID,
        LineTotal,
        ProductID,
        OrderQty   
FROM    Sales.SalesOrderHeader SOH,Sales.SalesOrderDetail SOD
WHERE   SOH.SalesOrderID = SOD.SalesOrderID
AND     (SOH.SalesOrderID=43659 or SOH.SalesOrderID=43661)
ORDER BY [OrderHeader!1!SalesOrderID], [SalesPerson!2!SalesPersonID],
         [OrderDetail!3!SalesOrderID],[OrderDetail!3!LineTotal]
FOR XML EXPLICIT

Voici le résultat partiel :

<OrderHeader SalesOrderID="43659" OrderDate="2001-07-01T00:00:00" CustomerID="676">
  <SalesPerson SalesPersonID="279" />
  <OrderDetail SalesOrderID="43659" LineTotal="10.373000" ProductID="712" OrderQty="2" />
  <OrderDetail SalesOrderID="43659" LineTotal="28.840400" ProductID="716" OrderQty="1" />
  <OrderDetail SalesOrderID="43659" LineTotal="34.200000" ProductID="709" OrderQty="6" />
   ...
</OrderHeader>
<OrderHeader SalesOrderID="43661" OrderDate="2001-07-01T00:00:00" CustomerID="442">
  <SalesPerson SalesPersonID="282" />
  <OrderDetail SalesOrderID="43661" LineTotal="20.746000" ProductID="712" OrderQty="4" />
  <OrderDetail SalesOrderID="43661" LineTotal="40.373000" ProductID="711" OrderQty="2" />
   ...
</OrderHeader>

E. Spécification des directives ID et IDREF

Cet exemple est le même que l'exemple C, à la seule différence que la requête spécifie les directives ID et IDREF. Ces directives remplacent les types de l'attribut SalesPersonID des éléments <OrderHeader> et <OrderDetail>. Il en résulte des liens à l'intérieur du document. Vous avez besoin du schéma pour visualiser les types remplacés. Par conséquent, la requête spécifie l'option XMLDATA dans la clause FOR XML pour extraire le schéma.

SELECT  1 as Tag,
        0 as Parent,
        SalesOrderID  as [OrderHeader!1!SalesOrderID!id],
        OrderDate     as [OrderHeader!1!OrderDate],
        CustomerID    as [OrderHeader!1!CustomerID],
        NULL          as [SalesPerson!2!SalesPersonID],
        NULL          as [OrderDetail!3!SalesOrderID!idref],
        NULL          as [OrderDetail!3!LineTotal],
        NULL          as [OrderDetail!3!ProductID],
        NULL          as [OrderDetail!3!OrderQty]
FROM   Sales.SalesOrderHeader
WHERE  SalesOrderID=43659 or SalesOrderID=43661
UNION ALL 
SELECT 2 as Tag,
       1 as Parent,
        SalesOrderID, 
        NULL,
        NULL,
        SalesPersonID,  
        NULL,         
        NULL,         
        NULL,
        NULL         
FROM   Sales.SalesOrderHeader
WHERE  SalesOrderID=43659 or SalesOrderID=43661
UNION ALL
SELECT 3 as Tag,
       1 as Parent,
        SOD.SalesOrderID,
        NULL,
        NULL,
        SalesPersonID,
        SOH.SalesOrderID,
        LineTotal,
        ProductID,
        OrderQty   
FROM    Sales.SalesOrderHeader SOH,Sales.SalesOrderDetail SOD
WHERE   SOH.SalesOrderID = SOD.SalesOrderID
AND     (SOH.SalesOrderID=43659 or SOH.SalesOrderID=43661)
ORDER BY [OrderHeader!1!SalesOrderID!id], [SalesPerson!2!SalesPersonID],
         [OrderDetail!3!SalesOrderID!idref],[OrderDetail!3!LineTotal]
FOR XML EXPLICIT, XMLDATA

Le résultat partiel est le suivant. Dans le schéma, les directives ID et IDREF ont remplacé les types de données de l'attribut SalesOrderID des éléments <OrderHeader> et <OrderDetail>. Si vous supprimez ces directives, le schéma renvoie les types d'origine de ces attributs.

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="OrderHeader" content="mixed" model="open">
    <AttributeType name="SalesOrderID" dt:type="id" />
    <AttributeType name="OrderDate" dt:type="dateTime" />
    <AttributeType name="CustomerID" dt:type="i4" />
    <attribute type="SalesOrderID" />
    <attribute type="OrderDate" />
    <attribute type="CustomerID" />
  </ElementType>
  <ElementType name="SalesPerson" content="mixed" model="open">
    <AttributeType name="SalesPersonID" dt:type="i4" />
    <attribute type="SalesPersonID" />
  </ElementType>
  <ElementType name="OrderDetail" content="mixed" model="open">
    <AttributeType name="SalesOrderID" dt:type="idref" />
    <AttributeType name="LineTotal" dt:type="number" />
    <AttributeType name="ProductID" dt:type="i4" />
    <AttributeType name="OrderQty" dt:type="i2" />
    <attribute type="SalesOrderID" />
    <attribute type="LineTotal" />
    <attribute type="ProductID" />
    <attribute type="OrderQty" />
  </ElementType>
</Schema>
<OrderHeader xmlns="x-schema:#Schema1" SalesOrderID="43659" OrderDate="2001-07-01T00:00:00" CustomerID="676">
  <SalesPerson SalesPersonID="279" />
  <OrderDetail SalesOrderID="43659" LineTotal="10.373000" ProductID="712" OrderQty="2" />
  ...
</OrderHeader>
...

F. Spécification des directives ID et IDREFS

Un attribut d'élément peut être spécifié comme attribut de type ID et l'attribut IDREFS peut ensuite être utilisé pour y faire référence. Cela permet de créer des liens à l'intérieur du document et est assimilable aux relations entre clés primaires et clés étrangères dans les bases de données relationnelles.

Cet exemple illustre l'utilisation des directives ID et IDREFS pour créer des attributs de types ID et IDREFS. Étant donné que les ID ne peuvent pas être des valeurs entières, dans cet exemple, leurs valeurs sont converties. En d'autres termes, ils subissent une conversion de type. Des préfixes sont utilisés pour les valeurs d'ID.

Supposons que vous souhaitiez construire un document XML comme suit :

<Customer CustomerID="C1" SalesOrderIDList=" O11 O22 O33..." >
    <SalesOrder SalesOrderID="O11" OrderDate="..." />
    <SalesOrder SalesOrderID="O22" OrderDate="..." />
    <SalesOrder SalesOrderID="O33" OrderDate="..." />
    ...
</Customer>

L'attribut SalesOrderIDList de l'élément < Customer > est un attribut à valeurs multiples qui fait référence à l'attribut SalesOrderID de l'élément < SalesOrder >. Pour établir ce lien, vous devez déclarer l'attribut SalesOrderID comme étant de type ID et l'attribut SalesOrderIDList de l'élément < Customer> comme étant de type IDREFS. Comme un client peut passer plusieurs commandes, le type IDREFS est utilisé.

En outre, IDREFS possède plusieurs valeurs. Par conséquent, vous devez recourir à une clause de sélection distincte qui réutilisera les mêmes informations de colonne de balise, de parent et de clé. La clause ORDER BY doit faire en sorte que la séquence des lignes qui composent les valeurs IDREFS apparaisse regroupée sous leur élément parent.

La requête ci-après génère le document XML souhaité. Elle utilise les directives ID et IDREFS pour remplacer les types dans les noms de colonnes (SalesOrder!2!SalesOrderID!ID, Customer!1!SalesOrderIDList!IDREFS).

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 C, Sales.SalesOrderHeader SOH
WHERE  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 C, Sales.SalesOrderHeader SOH
WHERE  C.CustomerID = SOH.CustomerID
ORDER BY [Customer!1!CustomerID] ,
         [SalesOrder!2!SalesOrderID!ID],
         [Customer!1!SalesOrderIDList!IDREFS]
FOR XML EXPLICIT

G. Utilisation de la directive hide pour masquer des éléments et des attributs dans le document XML obtenu

Cet exemple illustre l'utilisation de la directive hide. Cette directive est utile quand vous souhaitez que la requête renvoie un attribut pour classer les lignes de la table universelle renvoyée par la requête, sans que cet attribut figure dans le document XML obtenu au final.

Cette requête construit le document XML suivant :

<ProductModel ProdModelID="19" Name="Mountain-100">
  <Summary>
    <SummaryDescription>
           <Summary> element from XML stored in CatalogDescription column
    </SummaryDescription>
  </Summary>
</ProductModel>

Cette requête génère le document XML souhaité. Elle identifie deux groupes de colonnes possédant 1 et 2 comme valeurs Tag dans les noms de colonnes.

Cette requête utilise la méthode query() (type de données xml) du type de données xml pour interroger la colonne CatalogDescription de type xml afin d'extraire la description résumée. Elle utilise également la méthode value() (type de données xml) du type de données xml pour extraire la valeur ProductModelID de la colonne CatalogDescription. Cette valeur n'est pas requise dans le document XML obtenu, mais elle est nécessaire pour trier l'ensemble de lignes obtenu. Par conséquent, le nom de colonne, [Summary!2!ProductModelID!hide], comprend la directive hide. Si cette colonne n'est pas incluse dans l'instruction SELECT, vous devez trier l'ensemble de lignes par [ProductModel!1!ProdModelID] et [Summary!2!SummaryDescription], de type xml, et vous ne pouvez pas utiliser la colonne de type xml dans la clause ORDER BY. Par conséquent, la colonne [Summary!2!ProductModelID!hide] est ajoutée puis spécifiée dans la clause ORDER BY.

SELECT  1 as Tag,
        0 as Parent,
        ProductModelID     as [ProductModel!1!ProdModelID],
        Name               as [ProductModel!1!Name],
        NULL               as [Summary!2!ProductModelID!hide],
        NULL               as [Summary!2!SummaryDescription]
FROM    Production.ProductModel
WHERE   CatalogDescription is not null
UNION ALL
SELECT  2 as Tag,
        1 as Parent,
        ProductModelID,
        Name,
        CatalogDescription.value('
         declare namespace PD="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
       (/PD:ProductDescription/@ProductModelID)[1]', 'int'),
        CatalogDescription.query('
         declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
         /pd:ProductDescription/pd:Summary')
FROM    Production.ProductModel
WHERE   CatalogDescription is not null
ORDER BY [ProductModel!1!ProdModelID],[Summary!2!ProductModelID!hide]
FOR XML EXPLICIT
go

Voici le résultat obtenu :

<ProductModel ProdModelID="19" Name="Mountain-100">
  <Summary>
    <SummaryDescription>
      <pd:Summary xmlns:pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" >
        <p1:p xmlns:p1="http://www.w3.org/1999/xhtml">Our top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain. </p1:p>
      </pd:Summary>
    </SummaryDescription>
  </Summary>
</ProductModel>

H. Spécification de la directive element et du codage d'une entité

Cet exemple illustre la différence entre les directives element et xml. La directive element décompose les données en entités, contrairement à la directive xml. L'élément <Summary> reçoit des données XML, <Summary>This is summary description</Summary>, dans la requête.

Prenons par exemple la requête suivante :

SELECT  1 as Tag,
        0 as Parent,
        ProductModelID  as [ProductModel!1!ProdModelID],
        Name            as [ProductModel!1!Name],
        NULL            as [Summary!2!SummaryDescription!ELEMENT]
FROM    Production.ProductModel
WHERE   ProductModelID=19
UNION ALL
SELECT  2 as Tag,
        1 as Parent,
        ProductModelID,
        NULL,
       '<Summary>This is summary description</Summary>'
FROM   Production.ProductModel
WHERE  ProductModelID=19
FOR XML EXPLICIT

Voici le résultat obtenu : la description résumée est décomposée en entités dans le résultat.

<ProductModel ProdModelID="19" Name="Mountain-100">
  <Summary>
    <SummaryDescription>&lt;Summary&gt;This is summary description&lt;/Summary&gt;</SummaryDescription>
  </Summary>
</ProductModel>

Maintenant, si vous spécifiez la directive xml dans le nom de colonne (Summary!2!SummaryDescription!xml) au lieu de la directive element, vous obtenez la description résumée non décomposée en entités.

<ProductModel ProdModelID="19" Name="Mountain-100">
  <Summary>
    <SummaryDescription>
      <Summary>This is summary description</Summary>
    </SummaryDescription>
  </Summary>
</ProductModel>

Au lieu d'affecter une valeur XML statique, la requête suivante utilise la méthode query() du type xml pour extraire la description résumée des modèles de produit de la colonne CatalogDescription de type xml. Étant donné que le résultat doit être de type xml, aucune décomposition en entités n'est appliquée.

SELECT  1 as Tag,
        0 as Parent,
        ProductModelID  as [ProductModel!1!ProdModelID],
        Name            as [ProductModel!1!Name],
        NULL            as [Summary!2!SummaryDescription]
FROM    Production.ProductModel
WHERE   CatalogDescription is not null
UNION ALL
SELECT  2 as Tag,
        1 as Parent,
        ProductModelID,
        Name,
       (SELECT CatalogDescription.query('
            declare namespace pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
          /pd:ProductDescription/pd:Summary'))
FROM     Production.ProductModel
WHERE    CatalogDescription is not null
ORDER BY [ProductModel!1!ProdModelID],Tag
FOR XML EXPLICIT

I. Spécification de la directive cdata

Si la directive a pour valeur cdata, les données contenues ne sont pas codées comme une entité, mais placées dans la section CDATA. Les attributs cdata doivent être dépourvus de nom.

La requête suivante insère la description résumée des modèles de produits dans une section CDATA.

SELECT  1 as Tag,
        0 as Parent,
        ProductModelID  as [ProductModel!1!ProdModelID],
        Name            as [ProductModel!1!Name],
        '<Summary>This is summary description</Summary>'   
            as [ProductModel!1!!cdata] -- no attribute name so ELEMENT assumed
FROM    Production.ProductModel
WHERE   ProductModelID=19
FOR XML EXPLICIT

Voici le résultat obtenu :

<ProductModel ProdModelID="19" Name="Mountain-100">
   <![CDATA[<Summary>This is summary description</Summary>]]>
</ProductModel>

J. Spécification de la directive xmltext

Cet exemple illustre l'adressage des informations contenues dans la colonne de dépassement de capacité à l'aide de la directive xmltext dans une instruction SELECT utilisant le mode EXPLICIT.

Soit la table Person. Cette table possède une colonne nommée Overflow, qui stocke les données non consommées du document XML.

CREATE TABLE Person(PersonID varchar(5), PersonName varchar(20), Overflow nvarchar(200))
INSERT INTO Person VALUES ('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
INSERT INTO Person VALUES ('P2','Joe',N'<SomeTag attr2="data"/>')
INSERT INTO Person VALUES ('P3','Joe',N'<SomeTag attr3="data" PersonID="P">content</SomeTag>')

La requête suivante extrait des colonnes de la table Person. Concernant la colonne Overflow, AttributeName n'est pas spécifié, mais directive a pour valeur xmltext et fournit une composante du nom d'une colonne de la table universelle.

SELECT 1 as Tag, NULL as parent,
       PersonID as [Parent!1!PersonID],
       PersonName as [Parent!1!PersonName],
       overflow as [Parent!1!!xmltext] -- No AttributeName; xmltext directive
FROM Person
FOR XML EXPLICIT

Dans le document XML obtenu :

  • Comme AttributeName n'est pas spécifié pour la colonne Overflow et que la directive xmltext est spécifiée, les attributs de l'élément <overflow> sont ajoutés à la liste d'attributs de l'élément d'encadrement <Parent>.
  • En raison du conflit entre l'attribut PersonID de l'élément <xmltext> et l'attribut PersonID extrait du même niveau d'éléments, l'attribut de l'élément <xmltext> est ignoré (même si PersonID vaut NULL). En général, un attribut remplace un attribut de même nom dans les données en excès.

Voici le résultat obtenu :

<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">content</Parent>

Si les données en excès contiennent des sous-éléments et que la même requête est exécutée, les sous-éléments contenus dans la colonne Overflow sont ajoutés en tant que sous-éléments de l'élément d'encadrement <Parent>.

Supposons, par exemple, que les données de la table Person soient modifiées afin que la colonne Overflow contienne des sous-éléments.

TRUNCATE TABLE Person
INSERT INTO Person VALUES ('P1','Joe',N'<SomeTag attr1="data">content</SomeTag>')
INSERT INTO Person VALUES ('P2','Joe',N'<SomeTag attr2="data"/>')
INSERT INTO Person VALUES ('P3','Joe',N'<SomeTag attr3="data" PersonID="P"><name>PersonName</name></SomeTag>')

Si la même requête est exécutée, les sous-éléments de l'élément <xmltext> sont ajoutés en tant que sous-éléments de l'élément d'encadrement <Parent> :

SELECT 1 as Tag, NULL as parent,
       PersonID as [Parent!1!PersonID],
       PersonName as [Parent!1!PersonName],
       overflow as [Parent!1!!xmltext] -- no AttributeName, xmltext directive
FROM Person
FOR XML EXPLICIT

Voici le résultat obtenu :

<Parent PersonID="P1" PersonName="Joe" attr1="data">content</Parent>
<Parent PersonID="P2" PersonName="Joe" attr2="data"></Parent>
<Parent PersonID="P3" PersonName="Joe" attr3="data">
  <name>PersonName</name>
</Parent>

Si AttributeName est spécifié avec la directive xmltext, les attributs de l'élément <overflow> sont ajoutés en tant qu'attributs des sous-éléments de l'élément d'encadrement <Parent>. Le nom spécifié pour AttributeName devient le nom du sous-élément.

Dans la requête suivante, AttributeName, <overflow>, est spécifié, ainsi que la directive xmltext :

SELECT 1 as Tag, NULL as parent,
       PersonID as [Parent!1!PersonID],
       PersonName as [Parent!1!PersonName],
       overflow as [Parent!1!overflow!xmltext] -- overflow is AttributeName
                      -- xmltext is directive
FROM Person
FOR XML EXPLICIT

Voici le résultat obtenu :

<Parent PersonID="P1" PersonName="Joe">
  <overflow attr1="data">content</overflow>
</Parent>
<Parent PersonID="P2" PersonName="Joe">
  <overflow attr2="data" />
</Parent>
<Parent PersonID="P3" PersonName="Joe">
  <overflow attr3="data" PersonID="P">
    <name>PersonName</name>
  </overflow>
</Parent>

Dans l'élément de requête suivant, l'argument directive est spécifié pour l'attribut PersonName. Par conséquent, PersonName est ajouté en tant que sous-élément de l'élément d'encadrement <Parent>. Les attributs de <xmltext> sont néanmoins ajoutés à l'élément d'encadrement <Parent>. Le contenu de l'élément <overflow>, les sous-éléments, sont ajoutés avant les autres sous-éléments des éléments d'encadrement <Parent>.

SELECT 1      as Tag, NULL as parent,
       PersonID   as [Parent!1!PersonID],
       PersonName as [Parent!1!PersonName!element], -- element directive
       overflow   as [Parent!1!!xmltext]
FROM Person
FOR XML EXPLICIT

Voici le résultat obtenu :

<Parent PersonID="P1" attr1="data">content<PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P2" attr2="data">
  <PersonName>Joe</PersonName>
</Parent>
<Parent PersonID="P3" attr3="data">
  <name>PersonName</name>
  <PersonName>Joe</PersonName>
</Parent>

Si les données de la colonne xmltext contiennent des attributs sur l'élément racine, ces attributs ne sont pas montrés dans le schéma de données XML et l'analyseur MSXML ne valide pas le fragment de document XML obtenu. Exemple :

SELECT 1 as Tag,
       0 as Parent,
       N'<overflow a="1"/>' as 'overflow!1!!xmltext'
FOR XML EXPLICIT, xmldata

Voici le résultat obtenu : Notez que l'attribut en excès a ne figure pas dans le schéma renvoyé :

<Schema name="Schema2" 
        xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="overflow" content="mixed" model="open">
  </ElementType>
</Schema>
<overflow xmlns="x-schema:#Schema2" a="1">
</overflow>

Voir aussi

Référence

Utilisation du mode RAW
Utilisation du mode AUTO
Construction de code XML à l'aide de FOR XML

Concepts

Utilisation du mode PATH

Autres ressources

SELECT (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005