SQL Server 2014 – XQuery – obtient une list séparée par des virgules

J'ai une table de database dans SQL Server 2014 avec seulement une colonne ID ( int ) et une colonne xmldata de type XML .

Cette colonne xmldata contient par exemple:

 <book> <title>a nice Novel</title> <author>Maria</author> <author>Peter</author> </book> 

Comme prévu, j'ai plusieurs livres, donc plusieurs lignes avec xmldata .

Je veux maintenant exécuter une requête pour tous les livres, où Peter est un auteur. J'ai essayé cela dans certains testeurs xPath2.0 et suis arrivé à la conclusion que:

 /book/author/concat(text(), if(position() != last())then ',' else '') 

travaux.

Si vous essayez de porter ce succès dans SQL Server 2014 Express, il ressemble à ceci, qui est une syntaxe correctement échappée, etc .:

 SELECT id FROM books WHERE 'Peter' IN (xmldata.query('/book/author/concat(text(), if(position() != last())then '','' else '''')')) 

SQL Server ne semble cependant pas supporter une construction comme /concat(...) cause de:

La syntaxe XQuery '/ function ()' n'est pas supscope.

Je suis à la perte alors, pourquoi /text() fonctionnerait dans:

 SELECT id, xmldata.query('/book/author/text()') FROM books 

ce qu'il fait.

Mes contraintes:

  • Je suis lié à utiliser SQL Server
  • Je suis lié à xpath ou à autre chose qui peut être "injecté" comme indiqué ci-dessus (si la structure du xml ou de la database change, le xpath ci-dessus pourrait être isolé et la logique d'application qui construit la clause Where ne sera pas touché) VOIR EDITER

Y a-t-il un moyen de faire ce travail?

Cordialement,

BillDoor

MODIFIER:

Ma deuxième contrainte se résume à ceci:

Une application construit la clause Where par

 expression <operator> value(s) 

expression est stockée dans une database et est mappée par le xmlTag par exemple:

  | tokenname| queryssortingng | "author" | "xmldata.query(/book/author/text())" 

les valeurs sont présentées par l'user requestur. donc si l'user request l'auteur "Peter" avec l'opérateur "EQUALS", l'application construit:

 xmaldata.query(/book/author/text()) = "Peter" 

comme où la clause.

Si le client décide maintenant que l'auteur doit être nested dans un élément <authors> , je peux simplement changer l'expression dans la database de construction et la machine continue de fonctionner sans modification du code, simplement gérable.

J'ai donc besoin d'un moyen d'y parvenir

 <xPath> <operator> "Peter" 

ou toute autre combinaison de ces trois composantes isolées (voir ci-dessus: "Peter" IN <xPath>... ) m'obtient tous les livres de Peters, même s'il y a plusieurs auteurs non sortingés.

Cela ne suffirait pas non plus (ce n'est pas la syntaxe sqlserver, mais vous avez l'idée):

 WHERE xmldata.exist('/dossier/client[text() = "$1"]', "Peter") = 1; 

parce que l'opérateur est toujours nested dans l'expression, je ne pouvais pas requestr <> "Peter" .

Je sais que c'est étrange, s'il vous plaît ne pas remettre en question le concept dans son set – il a une histoire: /

EDIT: clarification supplémentaire:

Les règles de filter entrent dans l'application dans une structure XML essentiellement:

  • Opérateur: "EQ"
  • nom de domaine"
  • valeur "Peter"

évalue à:

  • expression = lookupExpressionForField("name") -> "table2.xmldata.value ('livre / auteur / nom [1]', 'varchar')"
  • operator = lookUpOperatorMapping("EQ") -> "="
  • value = FormatValues("Peter") -> "Peter" (si plusieurs valeurs sont passées FormatValues ​​cosntruit une list séparée par des virgules)

l'application génère ensuite: – constructClause(Ssortingng expression,Ssortingng operator,Ssortingng value)

"table2.xmldata.value ('book / author / name [1]', 'varchar')" + "=" + "Pierre"

puis construit une instruction Select avec le résultat en tant que clause WHERE.

il ne le construit pas comme ceci, non échappé, non filtré pour l'injection etc., mais c'est l'idée de base.

Je peux influencer la façon dont l'input est Transalted, ce qui signifie que je peux mettre en œuvre les methods:

  • lookupExpressionForField(Ssortingng field)
  • lookUpOperatorMapping(Ssortingng operator)
  • Formatvalues(List<Ssortingng> values) | Formatvalues(Ssortingng value)
  • constructClause(Ssortingng expression,Ssortingng operator,Ssortingng value)

cependant je choisis de faire, je peux changer les types de paramètre, je peux librement les implémenter. Le less le meilleur bien sûr. Construire simplement une list séparée par des virgules avec xPath serait optimal (comme si je pouvais quelque part cocher "enable / function () – syntaxe dans xPath" dans sqlserver et que / concat (if …) fonctionnerait)

Que diriez-vous quelque chose comme ça:

 SET NOCOUNT ON; DECLARE @Books TABLE (ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, BookInfo XML); INSERT INTO @Books (BookInfo) VALUES (N'<book> <title>a nice Novel</title> <author>Maria</author> <author>Peter</author> </book>'); INSERT INTO @Books (BookInfo) VALUES (N'<book> <title>another one</title> <author>Bob</author> </book>'); SELECT * FROM @Books bk WHERE bk.BookInfo.exist('/book/author[text() = "Peter"]') = 1; 

Cela renvoie uniquement la première input "livre". De là, vous pouvez extraire n'importe quelle partie du champ XML en utilisant la fonction "value".

La fonction "existe" renvoie un boolean / BIT. Cela va parcourir tous les nœuds "auteur" dans "book", il n'est donc pas nécessaire de concaténer dans une list séparée par des virgules pour l'utiliser dans une list IN, ce qui ne fonctionnerait pas de toute façon ;-).

Pour plus d'informations sur les fonctions "value" et "exists", ainsi que sur les autres fonctions à utiliser avec datatables XML, veuillez consulter:

xml Méthodes de type de données