J'ai une table avec une colonne XML. La vaste majorité des lignes contient ces données:
select * from DataResults where cast(baserentamount as varchar(250)) not like '<X C="1"></X>'
Je voudrais trouver une ligne parmi les millions qui ne correspond pas à cela. Donc ...
<X C="1"></X>
Ceci renvoie chaque ligne. Je suppose qu'il y a quelque chose de très simple qui ne va pas ici?
Mise à jour: peut-être devrais-je inverser le sens de cette question - quelle est la manière la plus simple de faire la requête que je veux, IE, correspond à l'entrée dans une colonne XML dans un WHERE?
4 Réponses :
Vous devriez regarder le XML réel dans votre tableau. Si vous insérez ce xml dans une table, ce n'est pas la même chaîne que vous pensez que c'est.
declare @Something table(baserentamount xml)
insert @Something select '<X C="1"></X>'
select *
, cast(baserentamount as varchar(250))
from @Something
Par conséquent, votre requête renverra très probablement chaque ligne. Et il renverra certainement les lignes que vous souhaitez exclure car la chaîne xml a changé.
Pouvez-vous être plus précis sur la façon de «regarder le XML réel dans votre tableau»? Je n'ai pas créé le tableau ni les données, tout ce que je sais, c'est que lorsque je sélectionne le casting, j'obtiens exactement la chaîne ci-dessus - j'ai coupé et collé à partir de SSMS.
Le xml que vous avez publié ne générera pas la même chaîne de sortie. Exécutez le code que j'ai publié et voyez par vous-même. Ou votre type de données de colonne n'est-il pas vraiment xml?
C'est vraiment du XML. Essayons le champ suivant que je vais devoir tester. Il contient
D'ACCORD. Eh bien, la chaîne "
Je l'ai fait, et j'ai essayé le XML "réel" et il ne correspondait pas non plus. Tout comme la chaîne plus longue que j'ai postée.
Hmmm fonctionne très bien si vous modifiez le prédicat where pour qu'il corresponde à la représentation sous forme de chaîne réelle de votre xml. Où .... '
Pas pour moi. Une autre façon de faire la requête? Je ne suis pas marié au CAST.
Fonctionne parfaitement sur trois instances différentes (toutes les différentes versions de serveur sql) et sur sql fiddle. sqlfiddle.com/#!18/c9fc4/2
Ahhh, eh bien ça y est, le diable est dans les détails:
XML tel que présenté:
<X C="1"/>
XML après CAST:
<X C="1" />
Pas tout à fait évident, ni évident pourquoi cela devrait être, mais ça y est, c'est un problème WS.
C'est ce que l'autre réponse vous dit, je crois.
Vous ne devez utiliser aucune approche basée sur des chaînes lorsque vous traitez avec XML. Beaucoup plus de détails pour que le diable s'endorme ;-) Je viens d'ajouter une réponse ...
La manière purement XML de gérer ce dont vous avez besoin serait de créer un petit schéma XML. Après cela, vous pouvez valider cette colonne XML par rapport à elle.
SQL
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ID | Reason | +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | XML Validation: Element or attribute 'C' was defined as fixed, the element value has to be equal to value of 'fixed' attribute specified in definition. Location: /*:X[1]/@*:C | | 3 | XML Validation: Declaration not found for element 'root'. Location: /*:root[1] | +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sortie
USE tempdb;
GO
-- DDL and sample data population, start
IF EXISTS (SELECT * FROM sys.xml_schema_collections
WHERE name = N'MySchema'
AND schema_id = SCHEMA_ID(N'dbo'))
DROP XML SCHEMA COLLECTION dbo.MySchema;
CREATE XML SCHEMA COLLECTION dbo.MySchema
AS N'<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="X">
<xsd:complexType>
<xsd:attribute name="C" type="xsd:int" use="required" fixed="1"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
DECLARE @validationTbl TABLE (ID INT NOT NULL, Reason NVARCHAR(1024) NULL);
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<X C="1"></X>') -- good
,(N'<X C="1"/>') -- good
,(N'<X C="2"></X>') -- bad
,(N'<root>Miami</root>'); -- ugly
-- DDL and sample data population, end
-- Method #1
DECLARE @ID INT, @XML AS XML(dbo.MySchema)
, @RowCount INT = (SELECT COUNT(*) FROM @tbl);
WHILE @RowCount > 0
BEGIN
BEGIN TRY
SELECT @ID = ID, @XML = XMLData
FROM @tbl
ORDER BY ID DESC
OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
END TRY
BEGIN CATCH
INSERT INTO @validationTbl (ID, Reason)
VALUES (@ID, ERROR_MESSAGE());
END CATCH
SET @RowCount -= 1;
END;
-- test
SELECT * FROM @validationTbl;
-- Method #2
-- unfortunately, it stops at the very first error
-- TRY_CAST() shall swallow all XSD validation errors internally
-- and produce a NULL value for all invalid rows
/*
Validate Expressions (XQuery)
https://docs.microsoft.com/en-us/sql/xquery/validate-expressions-xquery?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15
*/
SELECT TOP(4) *, TRY_CAST(xmldata AS XML(dbo.MySchema)) AS Result
FROM @tbl
ORDER BY ID;
Toute approche pour faire cela au niveau de la chaîne est erronée (en raison de différentes dispositions XML, qui sont sémantiquement égales et donc possibles à n'importe quel stade).
Essayez quelque chose comme ceci:
+----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | ID | xmldata | MoreThanOneNodeInLevel1 | MoreThanOnAttributeInFirstElement | Attribute_X_C_isNot1 | FirstElementIsNotNamedX | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 1 | <X C="1" /> | NULL | NULL | NULL | NULL | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 2 | <X A="huh?" C="1" /> | NULL | X | NULL | NULL | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 3 | <X C="2" /> | NULL | NULL | X | NULL | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 4 | <X C="1" /><X C="2" /> | X | NULL | NULL | NULL | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 5 | <Y C="1" /> | NULL | NULL | NULL | X | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+ | 6 | <root>some other</root> | NULL | NULL | NULL | X | +----+-------------------------+-------------------------+-----------------------------------+----------------------+-------------------------+
J'ai réfléchi à cette approche avant même de suggérer d'utiliser le schéma XML pour la validation. XML Schema le fait de manière déclarative, avec un seul résultat: valide ou non. En revanche, toutes les règles XPath sont procédurales. Et lorsque XML est un peu plus complexe, le nombre de règles XPath augmentera rapidement et nous devons toutes les vérifier par ligne.
@YitzhakKhabinsky, je ne sais pas comment la vérification de schéma est exécutée en interne, mais je suis presque sûr que c'est aussi une action assez lourde ... De plus, je n'aime pas la boucle WHILE ... Utiliser ESSAYEZ ... CATCH pour valider certaines entrées sont assez lourdes aussi ... Eh bien, comme toujours: cela dépend ... Votre approche de schéma est très directe , pas mal du tout, cependant. Mais je préférerais une approche avec plus de commentaires, quelle règle a été violée. Si l'OP a besoin d'un simple résultat vrai / faux , toutes ces règles peuvent être combinées en une seule XQuery .
Shnugo, j'ai ajouté la méthode n ° 2 à ma réponse. Vérifiez-le. C'est ainsi que cela est censé fonctionner. Malheureusement, (1) SQL Server n'a qu'un stub dans la documentation pour la validation (2) TRY_CAST () ne fonctionne pas comme prévu. C'est pourquoi je l'ai simulé via une boucle WHILE .
À tous. SQL Server XML Stack est très éloigné des normes W3C actuelles. Pour faciliter la vie de tout le monde, veuillez voter la suggestion suivante: feedback.azure.com/forums/908035-sql-server/suggesions/.../a>
Vous n'êtes pas sûr à 100% de ce que vous essayez de sélectionner, mais je pense que vous avez probablement besoin d'un caractère générique si vous souhaitez exclure les lignes qui contiennent ce xml, pas comme '% %'