MS SQL Server
  1. MS SQL Server
  3. Obtenir la valeur parente à l'aide des nœuds () et de la valeur ()
Intereting Posts
Noms de table / colonne entre crochets, même si leur nom est composé de caractères légaux SQL Server 2016 pour JSON PATH renvoie une string au lieu d'un tableau lors de l'utilisation de l'instruction de requête Qu'est-ce qu'une manière maintenable de stocker de grands champs de text sans sacrifier la performance? Ajout d'une courbe de tendance à un graphique à barres dans Reporting Services Convertir int timestamp en DateTime en utilisant Entity Framework Le paramètre @x n'a pas été défini pour la procédure stockée … avec MS_SQL JDBC Cas SQL Server 2008 lors de la diffusion Pourquoi j'obtiens l'erreur quand voulez insert à la table dans le server de SQL? Remplir un tableau dynamic avec des valeurs d'un tableau Généraliser une fonction de search de table SQL en utilisant exec T-SQL – Comment searchr un nième caractère dans une string SQL Server 2008 searchr et replace des éléments dans la colonne XML SQL sub select si existe Clé étrangère SQL sur plusieurs tables Comment mettre à jour cette requête sans bouclage ou slider dans SQL Server 2005

Obtenir la valeur parente à l'aide des nœuds () et de la valeur ()

J'ai un extrait de document XML qui correspond à ce XSD: 

<xs:complexType name="QuestionType"> <xs:sequence> <xs:element name="questionId" type="xs:ssortingng" minOccurs="1" /> <xs:element name="questionDescription" type="xs:ssortingng" minOccurs="1" /> <xs:element name="questionHeader" type="xs:ssortingng" minOccurs="0" /> <xs:element name="questionLabel" type="xs:ssortingng" minOccurs="0" /> <xs:element name="version" type="xs:ssortingng" minOccurs="1" maxOccurs="1" /> <xs:element name="SubQuestion" type="QuestionType" minOccurs="0" maxOccurs="unbounded" /> </xs:sequence> </xs:complexType>

Cela définit récursivement un élément <Question> qui peut avoir un nombre <SubQuestion> éléments <SubQuestion> , tous deux de type QuestionType .

En utilisant SQL, je voudrais interroger le document une fois pour get un set de résultats unique avec toutes les questions et sous-questions . J'ai deux requêtes indépendantes en ce moment pour y parvenir ( notez que j'utilise NVarChar(1000) à des fins de test uniquement – elles seront plus adaptées en production, et que @X est une variable XML qui correspond au schéma ci-dessus ): 

 SELECT -- Top-level questions... C.value('questionId[1]', 'NVarChar(1000)') Id, NULL ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version FROM @X.nodes('//Question') X(C); SELECT -- Sub-questions... C.value('questionId[1]', 'NVarChar(1000)') Id, C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version FROM @X.nodes('//SubQuestion') X(C);

Je m'attendrais à ce que cela puisse être résolu en utilisant un CTE récursif, mais j'ai du mal à en mettre un set.

Étant donné que vous avez étiqueté cette question avec sql-server-2008 et que IMHO SQL Server 2008 prend en charge XQuery, je voudrais suggérer un "angle" différent: utilisez une expression XPath pour sélectionner les nœuds qui vous intéressent. 

 .//*[local-name(.) = 'Question' or local-name(.) = 'SubQuestion']

Veuillez noter que j'utilise la fonction XPath nom-local () dans le cas où vos données XML réelles ont des déclarations d'espace de noms.

J'ai créé un exemple de file XML pour tester l'expression ci-dessus: 

 <?xml version="1.0" encoding="UTF-8"?> <Questions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.acme.com" xsi:schemaLocation="sample.xsd"> <Question> <questionId>1</questionId> <questionDescription>Question 1</questionDescription> <version>1</version> <SubQuestion> <questionId>1.1</questionId> <questionDescription>Question 1.1</questionDescription> <version>1</version> <SubQuestion> <questionId>1.1.1</questionId> <questionDescription>Question 1.1.1</questionDescription> <version>1</version> <SubQuestion> <questionId>1.1.1.1</questionId> <questionDescription>Question 1.1.1.1</questionDescription> <version>1</version> </SubQuestion> <SubQuestion> <questionId>1.1.1.2</questionId> <questionDescription>Question 1.1.1.2</questionDescription> <version>1</version> </SubQuestion> </SubQuestion> <SubQuestion> <questionId>1.2</questionId> <questionDescription>Question 1.2</questionDescription> </SubQuestion> </SubQuestion> </Question> <Question> <questionId>2</questionId> <questionDescription>Question 2</questionDescription> <version>1</version> </Question> <Question> <questionId>3</questionId> <questionDescription>Question 3</questionDescription> <version>1</version> <SubQuestion> <questionId>3.1</questionId> <questionDescription>Question 3.1</questionDescription> <version>1</version> </SubQuestion> </Question> </Questions>

Évaluation de cette requête XQuery par rapport à cet exemple 

 declare namespace acme = "http://www.acme.com"; <AllQuestions> { for $question in .//*[local-name(.) = 'Question' or local-name(.) = 'SubQuestion'] return <Question> <questionId>{ data($question/acme:questionId) }</questionId> <questionDescription>{ data($question/acme:questionDescription) }</questionDescription> </Question> } </AllQuestions>

aura pour résultat 

 <?xml version="1.0" encoding="UTF-8"?> <AllQuestions> <Question> <questionId>1</questionId> <questionDescription>Question 1</questionDescription> </Question> <Question> <questionId>1.1</questionId> <questionDescription>Question 1.1</questionDescription> </Question> <Question> <questionId>1.1.1</questionId> <questionDescription>Question 1.1.1</questionDescription> </Question> <Question> <questionId>1.1.1.1</questionId> <questionDescription>Question 1.1.1.1</questionDescription> </Question> <Question> <questionId>1.1.1.2</questionId> <questionDescription>Question 1.1.1.2</questionDescription> </Question> <Question> <questionId>1.2</questionId> <questionDescription>Question 1.2</questionDescription> </Question> <Question> <questionId>2</questionId> <questionDescription>Question 2</questionDescription> </Question> <Question> <questionId>3</questionId> <questionDescription>Question 3</questionDescription> </Question> <Question> <questionId>3.1</questionId> <questionDescription>Question 3.1</questionDescription> </Question> </AllQuestions>

EDIT – Dernière requête 

 SELECT C.value('questionId[1]', 'NVarChar(1000)') Id, COALESCE( C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'), C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)') ) ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version FROM @X.nodes('.//*[local-name(.)="Question" or local-name(.)="SubQuestion"]') X(C);

Je fais cela jusqu'à présent, bien que j'espère encore compacter la requête un peu: 

 WITH Q AS ( SELECT C.value('questionId[1]', 'NVarChar(1000)') Id, NULL ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version FROM @X.nodes('//Question') X(C) UNION ALL SELECT C.value('questionId[1]', 'NVarChar(1000)') Id, COALESCE( C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'), C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)') ) ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version FROM @X.nodes('//SubQuestion') X(C) ) SELECT Q.Id, Q.ParentId, Q.Description, Q.Header, Q.Label, Q.Version FROM Q;

C'est le bit important, car il aura la première valeur ParentId non nulle: 

 COALESCE( C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)'), C.query('..').value('(SubQuestion/questionId)[1]', 'NVarChar(1000)') ) ParentId

Vous pouvez utiliser un CTE: 

 WITH TopLevel (ID, ParentID, Description, Header, Label,Level) AS ( SELECT -- Top-level questions... C.value('questionId[1]', 'NVarChar(1000)') Id, NULL ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version, 0 AS Level FROM @X.nodes('//Question') X(C) UNION ALL SELECT -- Sub-questions... C.value('questionId[1]', 'NVarChar(1000)') Id, C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') ParentId, C.value('questionDescription[1]', 'NVarChar(1000)') Description, NULLIF(C.value('questionHeader[1]', 'NVarChar(1000)'), '') Header, NULLIF(C.value('questionLabel[1]', 'NVarChar(1000)'), '') Label, C.value('version[1]', 'NVarChar(1000)') Version ,Level + 1 AS Level FROM @X.nodes('//SubQuestion') X(C) JOIN TopLevel AS t ON C.query('..').value('(Question/questionId)[1]', 'NVarChar(1000)') = t.id ) SELECT * FROM TopLevel

Référence: http://msdn.microsoft.com/en-us/library/ms186243.aspx