Sélectionner les valeurs d'un champ XML en SQL Server 2008

Question

En regardant mon champ XML, mes lignes ressemblent à ceci :

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

Notez qu'il s'agit de trois lignes dans mon tableau.

J'aimerais retourner un résultat SQL sous forme de tableau, comme dans le cas suivant

Jon  | Johnson
Kathy| Carter
Bob  | Burns

Quelle requête permettra d'atteindre cet objectif ?

Answer 1

Étant donné que le champ XML est nommé 'xmlField'...

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

Answer 2

Si l'on considère que les données XML proviennent d'une table 'table' et sont stockées dans une colonne 'field', il faut utiliser la fonction Méthodes XML extraire les valeurs avec xml.value() les nœuds de projet avec xml.nodes() utiliser CROSS APPLY à rejoindre :

SELECT 
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
    CROSS APPLY field.nodes('/person') t(p)

Vous pouvez vous débarrasser de la nodes() y cross apply si chaque champ contient exactement un élément "personne". Si le XML est une variable, vous sélectionnez FROM @variable.nodes(...) et vous n'avez pas besoin de la cross apply .

Answer 3

Ce post a été utile pour résoudre mon problème qui a un format XML un peu différent... mon XML contient une liste de clés comme l'exemple suivant et je stocke le XML dans la colonne SourceKeys dans une table nommée DeleteBatch :

<k>1</k>
<k>2</k>
<k>3</k>

Créez le tableau et remplissez-le avec des données :

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1, 
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2, 
    (CAST('<k>100</k><k>101</k>' AS XML))

Voici mon SQL pour sélectionner les clés à partir du XML :

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

Voici les résultats de la requête...

ExecutionKey    Key
1   1
1   2
1   3
2   100
2   101

Answer 4

Cela peut répondre à votre question :

select cast(xmlField as xml) xmlField into tmp from (
select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField
union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'
union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'
) tb

SELECT
    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName
FROM tmp

drop table tmp

Answer 5

Mince alors. C'était un fil de discussion très utile à découvrir.

J'ai quand même trouvé certaines de ces suggestions déroutantes. Chaque fois que j'ai utilisé value con [1] dans la chaîne, il ne récupère que la première valeur. Et certaines suggestions recommandaient d'utiliser cross apply qui (dans mes tests) a ramené beaucoup trop de données.

Voici donc mon exemple simple de la façon dont vous créez une xml puis lire ses valeurs dans un tableau.

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

Et voici le résultat :

C'est une syntaxe bizarre, mais avec un exemple décent, il est assez facile de l'ajouter à vos propres fonctions SQL Server.

En parlant de ça, voici le correct réponse à cette question.

En supposant que vous avez vos données xml dans un fichier @xml variable de type xml (comme le montre mon exemple ci-dessus), voici comment renvoyer les trois lignes de données du fichier xml cité dans la question :

SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)