2
votes

Requête SQL Xml pour Xpath

Tentative de retour de la valeur d'un champ XML via une requête Xpath.

Voici à quoi ressemble le XML dans un instantané.

  SET @return = CAST(CAST(@xmlData AS xml).query('ArrayOfCustomProperty/CustomProperty/Key[text()=sql:variable("@key")]/../Value/text()') AS nvarchar(255))

(désolé pour le XML mal formaté ... StackOverflows RCE a des problèmes avec le rendu. Le ArrayOfCustomProperty est fermé à la fin, juste pas affiché pour une raison quelconque)

Cette requête fonctionne pour moi .... p>

<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>

Cela me permet d'avoir une fonction où les paramètres sont @xmlData et @key pour ce qui doit être recherché. J'ai besoin d'une autre fonction (ou je pourrais modifier celle-ci) où je peux également rechercher le nœud [DeveloperId], donc un troisième paramètre sera passé en tant que @devId. J'ai essayé pas mal de choses différentes mais rien n'a encore fonctionné pour moi. Je voudrais une requête où je peux obtenir la [Valeur] lorsque [DeveloperId] et [Key] sont présents en utilisant la même structure (si possible) pour le fonctionnement de la requête Xpath actuelle.

Merci d'avance pour toute aide.

xml sql sql-server xpath

0 commentaires

3 Réponses :

1
votes

Vous pouvez convertir des données xml en une table, puis traiter la table. Quelque chose comme ça.

declare @xml xml=N'<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>',
@key varchar(50)

;with cte as (
select t.v.value('DeveloperId[1]','varchar(50)') DeveloperId,
    t.v.value('Key[1]','varchar(50)') [Key],
    t.v.value('Value[1]','varchar(50)') [Value]
from @xml.nodes('ArrayOfCustomProperty/CustomProperty') t(v)
)
select * from cte
where [Key] = @key

2 commentaires

Merci pour la réponse. Cela fonctionne, même si j'espérais utiliser la fonctionnalité de «requête» en ligne si possible. J'adapterai ma solution pour utiliser un tableau sauf si je vois une autre réponse. Merci encore.

Cela fonctionne correctement, mais peut être amélioré. Imaginez de gros XML avec beaucoup d'éléments . Votre approche doit tous les renvoyer, juste pour parcourir la plupart des résultats. Vous pouvez vérifier ma réponse comment vous pouvez utiliser un prédicat pour renvoyer uniquement l'élément nécessaire.

1
votes

Vous devriez plutôt essayer de renvoyer le moins possible du XML.
Le mieux était de réduire la quantité d'analyse que le moteur doit analyser au minimum nécessaire.
Et vous devez éviter la navigation arrière en utilisant ../ . C'est un tueur de performances bien connu.

Essayez ceci:

DECLARE @key VARCHAR(100)='ValidForLoyaltyPoints' ;
DECLARE @devId VARCHAR(100)='Test456';
--the Query

SELECT t.YourXml.value(N'(/ArrayOfCustomProperty
                          /CustomProperty[(DeveloperId/text())[1]=sql:variable("@devId") 
                                      and (Key/text())[1]=sql:variable("@key")]
                          /Value/text())[1]',N'nvarchar(100)')
FROM @tbl t;

--Votre variable-clé

SELECT cp.value('(DeveloperId/text())[1]','nvarchar(250)') AS DeveloperId
      ,cp.value('(Value/text())[1]','nvarchar(250)') AS Value
FROM @tbl t
CROSS APPLY t.YourXml.nodes('/ArrayOfCustomProperty/CustomProperty[(Key/text())[1]=sql:variable("@key")]') A(cp);

--la requête

DECLARE @key VARCHAR(100)='ValidForLoyaltyPoints' ;

Le XPath / XQuery dans .nodes () plongera jusqu'à et renverra celui avec un , où le texte de la clé est comme le filtre donné. Le trouvé est renvoyé. En utilisant .value () , nous pouvons lire les éléments sous cette propriété donnée.

UPDATE

Il semble que vous essayiez de récupérer le comme valeur scalaire en fonction de et . Vous pouvez utiliser un simple prédicat pour interroger les deux à la fois:

DECLARE @tbl TABLE(YourXml XML);
INSERT INTO @tbl VALUES
(N'<?xml version="1.0" encoding="utf-16"?>  
<ArrayOfCustomProperty xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">    
<CustomProperty>      
    <DeveloperId>Test123</DeveloperId>      
    <Key>AgreedToTerms</Key>      
    <Value>True</Value>    
</CustomProperty>    
<CustomProperty>      
    <DeveloperId>Test456</DeveloperId>      
    <Key>ValidForLoyaltyPoints</Key>      
    <Value>False</Value>    
</CustomProperty>  
</ArrayOfCustomProperty>');

Vous lisez ceci comme

Plongez dans le tableau
Plongez plus profondément dans CustomProperty
Rechercher un élément auquel correspond le filtre ...
... et renvoyer sa valeur

5 commentaires

Merci pour la réponse. Je vais faire quelques tests entre votre réponse et la précédente pour voir les résultats de performance. Merci également pour les informations supplémentaires.

@ JohnPete22 Super! Soyez si gentil et faites-nous savoir les différences ... En général, je dirais: plus vous avez de CP dans un , plus la différence augmentera ...

J'ai quelques problèmes avec la fonction (pas extrêmement compétent avec ces requêtes XML en particulier dans une fonction). La fonction prendra deux paramètres différents - @key et developerId. Je dois renvoyer la [Valeur] où key = [Key] et developerId = [DeveloperId]. À l'heure actuelle, cela renvoie le DeveloperId et la valeur. Est-il possible d'ajouter un autre Cross Apply (je n'arrive pas à le comprendre) pour le DeveloperID?

@ JohnPete22, Cela signifie que vous passez deux paramètres et que vous souhaitez renvoyer uniquement la valeur scalaire? Cela ne nécessite aucun appliquer . Juste les deux paramètres avec et dans le prédicat XQuery dans .value ()

Oui c'est correct. J'ai posté ce que je "pensais" que vous vouliez dire avec votre dernier commentaire en réponse.

0
votes

@Shnugo

Est-ce ce que vous vouliez dire avec votre dernier commentaire?

DECLARE @tbl TABLE(YourXml XML);
INSERT INTO @tbl VALUES (@xmlData)

SELECT t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/Value/text())[1]','nvarchar(250)') AS Value 
FROM @tbl t
WHERE t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/Key/text())[1]','nvarchar(250)') = @key 
AND
t.YourXml.value('(/ArrayOfCustomProperty/CustomProperty/DeveloperId/text())[1]','nvarchar(250)') = @devId

3 commentaires

@Shnugo ne sait pas si la balise dans la réponse a fonctionné, alors allez l'ajouter ici. Merci encore.

Salut John, j'ai ajouté quelque chose à ma réponse. Ni la balise dans la réponse, ni la balise dans le commentaire de cette réponse ne déclenchera une alerte. Parce que je n'ai pas participé ici.

Un autre conseil: si cela n'est pas censé être une réponse, vous devriez plutôt utiliser l'option d'édition de votre question pour ajouter à cela.