Sélection de la valeur null à partir de XML dans SQL Server

Je pense que si vous utilisez la fonction number (), vous obtiendrez null comme prévu. Cela ne fonctionne que pour les types de nombres:

 select ParamValues.TaskChainerTask.query('Property1').value('number(.)','int') as Property1, ParamValues.TaskChainerTask.query('Property2').value('number(.)','int') as Property2 from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask) 

http://go4answers.webhost4life.com/Example/including-null-columns-empty-elements-125474.aspx

 [not(@xsi:nil = "true")] 

Cela va sélectionner null. D'ailleurs, le code auteur a une faute de frappe

 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace" 

instance est mal orthographié comme instace

 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 

Version de travail du code auteur

 declare @a xml select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Element> <Property1>1</Property1> <Property2>1</Property2> </Element> <Element> <Property1 xsi:nil="true" /> <Property2>2</Property2> </Element> <Element> <Property1>3</Property1> <Property2>3</Property2> </Element> </TestSet>' select ParamValues.TaskChainerTask.value('./Property1[1][not(@xsi:nil = "true")]','int') as Property1, ParamValues.TaskChainerTask.value('./Property2[1][not(@xsi:nil = "true")]','int') as Property2 from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask) 

Comme vous définissez les champs sur INT, vous avez le problème que les deux champs xsi: nil = "true" et la valeur 0 finiront par 0 comme valeur par défaut pour INT.

Vous pouvez d'abord convertir en VARCHAR pour détecter la string vide ('') que les champs de string contenant xsi: nil = "true" produisent, puis convertissent le résultat en INT.

Ce SELECT vous donnera la réponse que vous êtes après

 SELECT CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property1').value('.', 'varchar(5)'),'')) AS Property1 , CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property2').value('.', 'varchar(5)'),'')) AS Property2 FROM @a.nodes('(/TestSet/Element)') AS ParamValues (TaskChainerTask) 

Le résultat de ceci vous donne:

 Property1 Property2 1 1 NULL 2 3 3 

J'ai simplement utilisé NULLIF pour transformer des strings vides en valeurs NULL si nécessaire.

Vous pouvez générer nil maintenant en utilisant FOR XML , mais je n'ai jamais trouvé comment l'parsingr pardon …

Je sougest cette approche:

 DECLARE @a XML = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace"> <Element> <Property1>1</Property1> <Property2>1</Property2> </Element> <Element> <Property1 xsi:nil="true" /> <Property2>2</Property2> </Element> <Element> <Property1>3</Property1> <Property2>3</Property2> </Element> </TestSet>' SELECT ParamValues.TaskChainerTask .value('./Property1[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property1, ParamValues.TaskChainerTask .value('./Property2[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property2 FROM @a.nodes('//Element') ParamValues(TaskChainerTask) 

Une façon intelligente de le faire serait de supprimer le nœud Property1 du XML où les valeurs NULL sont souhaitées. Donc, quel que soit le nœud que vous voulez être nul dans votre jeu de résultats, ne l'ajoutez pas au XML. De cette façon, vous n'aurez pas besoin d'append l'atsortingbut xsi: nill.

Donc, ci-dessous entraînera également un null:

 declare @a xml select @a = '<TestSet> <Element> <Property1>1</Property1> <Property2>1</Property2> </Element> <Element> //I have removed the property1 node and this will result in a null value <Property2>2</Property2> </Element> <Element> <Property1>3</Property1> <Property2>3</Property2> </Element> </TestSet>' select ParamValues.TaskChainerTask.value('./Property1[1]','int') as Property1, ParamValues.TaskChainerTask.value('./Property2[1]','int') as Property2 from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask) 

Il peut être vu dans l'exemple ci-dessus qu'il n'y a pas de nœud Property1 donc il en résultera une valeur nulle

Je ne suis pas sûr si votre cas particulier vous oblige à interroger les nœuds en premier, mais sinon vous pouvez requestr le .value et fournir le xPath. Puisque le noeud Property1 existe, vous voulez évaluer le text () du noeud Property1 et non le noeud lui-même:

  select ParamValues.TaskChainerTask.value('Property1[1]/text()[1]','int') as Property1, ParamValues.TaskChainerTask.value('Property2[1]/text()[1]','int') as Property2 from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask) 

En plus de vous assurer que cela fonctionne dans d'autres cas, vous pouvez fournir le path le plus détaillé dans l'instruction @ a.nodes xPath et avancer avec "../" au lieu de sous-interroger les résultats du nœud.