Colonnes avec un nom

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les conditions suivantes sont celles dans lesquelles les colonnes de l'ensemble de lignes avec nom sont mappées, avec respect de la casse, au document XML obtenu :

• Le nom de colonne commence par un arobase (@).

• Le nom de colonne ne commence pas par un signe (@).

• Le nom de colonne ne commence pas par un signe (@) et contient une barre oblique (/).

• Plusieurs colonnes partagent le même préfixe.

• Une colonne porte un nom différent.

Le nom de colonne commence par un signe (@)

Si le nom de colonne commence par un signe à signe (@) et ne contient pas de barre oblique (/), un attribut de l’élément row qui a la valeur de colonne correspondante est créé. Par exemple, la requête suivante retourne un ensemble de lignes à deux colonnes (@PmId, Name). Dans le code XML résultant, un PmId attribut est ajouté à l’élément correspondant row et une valeur lui ProductModelID est affectée.

SELECT ProductModelID as "@PmId",
       Name
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH;

Voici le résultat obtenu :

<row PmId="7">
  <Name>HL Touring Frame</Name>
</row>

Les attributs doivent être fournis avant tous les autres types de nœuds, tels que les nœuds d’élément et les nœuds de texte, au même niveau. La requête suivante renvoie une erreur :

SELECT Name,
       ProductModelID as "@PmId"
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH;

Le nom de colonne ne commence pas par un signe (@)

Si le nom de colonne ne commence pas par un signe (@), n’est pas l’un des tests de nœud XPath et ne contient pas de barre oblique (/), un élément XML qui est un sous-élément de l’élément de ligne, par défaut, row est créé.

La requête suivante spécifie le nom de colonne, qui est le résultat. Un élément enfant result est donc ajouté à l’élément row.

SELECT 2 + 2 as result
for xml PATH;

Voici le résultat obtenu :

<row>
  <result>4</result>
</row>

La requête suivante spécifie le nom de colonne, ManuWorkCenterInformationpour le code XML retourné par la requête XQuery spécifiée par rapport Instructions à la colonne de type xml . Un élément ManuWorkCenterInformation est donc ajouté en tant qu’enfant de l’élément row.

SELECT
  ProductModelID,
  Name,
  Instructions.query(
    'declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
     /MI:root/MI:Location
    ') as ManuWorkCenterInformation
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH;

Voici le résultat obtenu :

<row>
  <ProductModelID>7</ProductModelID>
  <Name>HL Touring Frame</Name>
  <ManuWorkCenterInformation>
    <MI:Location ...LocationID="10" ...></MI:Location>
    <MI:Location ...LocationID="20" ...></MI:Location>
     ...
  </ManuWorkCenterInformation>
</row>

Le nom de colonne ne commence pas par un signe (@) et contient une barre oblique (/)

Si le nom de colonne ne commence pas par un signe (@), mais contient une barre oblique (/), le nom de colonne indique une hiérarchie XML. Par exemple, si le nom de colonne est « Name1/Name2/Name3.../Namen », chaque nom i représente un nom d’élément imbriqué dans l’élément de ligne actuel (pour i = 1) ou qui se trouve sous l’élément portant le nom i-1. Si Namen commence par @, il est mappé à un attribut d’élément Namen-1 .

Par exemple, la requête suivante retourne un ID d’employé et un nom qui sont représentés sous la forme d’un élément EmpName complexe qui contient un First, Middleet Last un nom.

SELECT EmployeeID "@EmpID",
       FirstName  "EmpName/First",
       MiddleName "EmpName/Middle",
       LastName   "EmpName/Last"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID  AND
       E.EmployeeID = 1
FOR XML PATH;

Les noms de colonnes sont utilisés comme chemin d'accès dans la construction du document XML en mode PATH. Le nom de colonne qui contient des valeurs d’ID d’employé commence par « @ ». Par conséquent, un attribut, EmpIDest ajouté à l’élément row . Toutes les autres colonnes incluent une barre oblique (/) dans le nom de colonne qui indique la hiérarchie. Le document XML obtenu possède l’enfant EmpName sous l’élément row, et l’enfant EmpName possède les éléments enfants First, Middle et Last.

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Last>Achong</Last>
  </EmpName>
</row>

Le deuxième prénom de l'employé est NULL et, par défaut, la valeur NULL correspond à l'absence de l'élément ou de l'attribut. Si vous souhaitez que des éléments soient générés pour les valeurs NULL, vous pouvez spécifier la directive ELEMENTS avec XSINIL, comme le montre la requête ci-après.

SELECT EmployeeID "@EmpID",
       FirstName  "EmpName/First",
       MiddleName "EmpName/Middle",
       LastName   "EmpName/Last"
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.EmployeeID = C.ContactID  AND
       E.EmployeeID = 1
FOR XML PATH, ELEMENTS XSINIL;

Voici le résultat obtenu :

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Middle xsi:nil="true" />
    <Last>Achong</Last>
  </EmpName>
</row>

Par défaut, le mode PATH génère des données XML centrées sur l'attribut. Par conséquent, la spécification de la directive ELEMENTS dans une requête en mode PATH est sans effet. Toutefois, comme le montre l'exemple précédent, la directive ELEMENTS, associée à XSINIL, permet de générer des éléments pour les valeurs NULL.

Outre l'ID et le nom, la requête suivante extrait l'adresse d'un employé. Conformément au chemin d’accès indiqué dans les noms des colonnes d’adresses, un élément enfant Address est ajouté à l’élément row et les détails de l’adresse sont ajoutés en tant qu’éléments enfants de l’élément Address.

SELECT EmployeeID   "@EmpID",
       FirstName    "EmpName/First",
       MiddleName   "EmpName/Middle",
       LastName     "EmpName/Last",
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City         "Address/City"
FROM   HumanResources.Employee E,
       Person.Contact C,
       Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID = 1
FOR XML PATH;

Voici le résultat obtenu :

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
    <Last>Achong</Last>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <City>Monroe</City>
  </Address>
</row>

Plusieurs colonnes partagent le même préfixe de chemin d’accès

Si plusieurs colonnes suivantes partagent le même préfixe de chemin d’accès, elles sont regroupées sous le même nom. Si des préfixes d’espace de noms différents sont utilisés même s’ils sont liés au même espace de noms, un chemin d’accès est considéré comme différent. Dans la requête précédente, les FirstNamecolonnes et LastName les colonnes MiddleNamepartagent le même EmpName préfixe. Par conséquent, ils sont ajoutés en tant qu’enfants de l’élément EmpName . Cela était également le cas au moment de la création de l’élément Address dans l’exemple précédent.

Une colonne a un nom différent

Si une colonne intermédiaire et portant un nom différent apparaît, elle rompt le regroupement, comme le montre la requête modifiée suivante. La requête interrompt le regroupement des FirstNamecolonnes , MiddleNameet LastName, comme spécifié dans la requête précédente, en ajoutant des colonnes d’adresse entre les colonnes et MiddleName les FirstName colonnes.

SELECT EmployeeID "@EmpID",
       FirstName "EmpName/First",
       AddressLine1 "Address/AddrLine1",
       AddressLine2 "Address/AddrLIne2",
       City "Address/City",
       MiddleName "EmpName/Middle",
       LastName "EmpName/Last"
FROM   HumanResources.EmployeeAddress E,
       Person.Contact C,
       Person.Address A
WHERE  E.EmployeeID = C.ContactID
AND    E.AddressID = A.AddressID
AND    E.EmployeeID = 1
FOR XML PATH;

La requête crée donc deux éléments EmpName. Le premier élément EmpName possède l’élément enfant FirstName et le second élément EmpName possède les éléments enfants MiddleName et LastName.

Voici le résultat obtenu :

<row EmpID="1">
  <EmpName>
    <First>Gustavo</First>
  </EmpName>
  <Address>
    <AddrLine1>7726 Driftwood Drive</AddrLine1>
    <City>Monroe</City>
  </Address>
  <EmpName>
    <Last>Achong</Last>
  </EmpName>
</row>

Voir aussi

• Utiliser le mode PATH avec FOR XML