MS SQL Server
  1. MS SQL Server
  3. Travailler avec des nœuds XML nesteds dans SQL Server
Intereting Posts
Est-il possible dans SSRS d'intégrer l'input de l'user dans un rapport? Passer les parameters à OLE DB SOURCE insert un document Word dans la database SQL Server? Comment récupérer la valeur et l'utiliser dans une procédure stockée dans une requête SQL? sql combine deux colonnes pouvant avoir des valeurs nulles Requête de mise à jour SQL renvoyant une erreur Somme le time écoulé entre les horodatages SQL Server gestion des events dans c # .net winform application qui utilise le server sql Quelle est la différence entre les contraintes de key primaires, uniques et étrangères et les index? Transmettre le code XML à une procédure stockée dans un noeud Comment puis-je find les différences entre deux bases de données? convertir varchar en int dans une instruction select en utilisant patindex Configuration Hibernate pour SQLServer – Classe de pilote SQL: jointure de trois tables Jointure SQL sur deux tables non liées ou ayant des keys primaires

Travailler avec des nœuds XML nesteds dans SQL Server

Le XML est joint ci-dessous. La requête que j'utilise renvoie les lignes d'adresse croisées appliquées à chaque code postal.

Sortie incorrecte: 

Code Reaper PC1 PC1_AL1 PC1_AL2 Code Reaper PC1 PC2_AL1 PC2_AL2 Code Reaper PC1 PC3_AL1 PC3_AL2 ... 9 rows in total

Comment puis-je get la sortie attendue ci-dessous? Fondamentalement, je ne veux que des lignes d'adresse pour le code postal respectif à côté de lui. 

 Code Reaper PC1 PC1_AL1 PC1_AL2 Code Reaper PC2 PC2_AL1 PC2_AL2 Code Reaper PC3 PC3_AL1 PC3_AL2

C'est ce que j'essaie. 

 DECLARE @XMLDocument XML SET @XMLDocument = N'<People><Person> <PersonDetails> <Surname>Code</Surname> <Forename>Reaper</Forename> </PersonDetails> <HomeInformation> <Address> <PostCode>PC1</PostCode> <AddressLines> <AddressLine1>PC1_AL1</AddressLine1> <AddressLine2>PC1_AL2</AddressLine2> </AddressLines> </Address> <Address> <PostCode>PC2</PostCode> <AddressLines> <AddressLine1>PC2_AL1</AddressLine1> <AddressLine2>PC2_AL2</AddressLine2> </AddressLines> </Address> <Address> <PostCode>PC3</PostCode> <AddressLines> <AddressLine1>PC3_AL1</AddressLine1> <AddressLine2>PC3_AL2</AddressLine2> </AddressLines> </Address> </HomeInformation> </Person> </People> ' SELECT [Surname], [GivenName], [PostCode], [AddressLine1], [AddressLine2] FROM (SELECT ISNULL(Person.PersonDetails.value('Surname[1]', 'nvarchar(max)'),'') AS [Surname], ISNULL(Person.PersonDetails.value('Forename[1]', 'nvarchar(max)'),'') AS [GivenName], ISNULL(HomeInformation.[Address].value('PostCode[1]', 'nvarchar(max)'),'') AS [PostCode], ISNULL(HomeInformationAddress.AddressLines.value('AddressLine1[1]', 'nvarchar(max)'),'') AS [AddressLine1], ISNULL(HomeInformationAddress.AddressLines.value('AddressLine2[1]', 'nvarchar(max)'),'') AS [AddressLine2] FROM @XMLDocument.nodes('People/Person/PersonDetails') AS Person(PersonDetails) OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address]) OUTER APPLY PersonDetails.nodes('../HomeInformation/Address/AddressLines') HomeInformationAddress(AddressLines) ) as X

Ces deux lignes de votre clause FROM se rejoignent les unes les autres: 

 OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address]) OUTER APPLY PersonDetails.nodes('../HomeInformation/Address/AddressLines') HomeInformationAddress(AddressLines)

Vous devez rendre le second dépendant du premier pour éviter cela: 

 OUTER APPLY PersonDetails.nodes('../HomeInformation/Address') HomeInformation([Address]) OUTER APPLY HomeInformation.nodes('../AddressLines') HomeInformationAddress(AddressLines)

Vous devriez éviter la navigation en arrière. Il n'y a pas besoin de ../ du tout. Essayez d'aller plus en profondeur dans votre arborescence hiérarchique:

Le premier appel .nodes() reviendra avec tous les nœuds <Person> dans <People> . Le second appel à .nodes() renvoie avec les noeuds <Address> . Le dernier renvoie tous les éléments <AddressLine> . 

 SELECT ISNULL(prs.value('(PersonDetails/Surname/text())[1]', 'nvarchar(max)'),'') AS [Surname], ISNULL(prs.value('(PersonDetails/Forename/text())[1]', 'nvarchar(max)'),'') AS [GivenName], ISNULL(addr.value('(PostCode/text())[1]', 'nvarchar(max)'),'') AS [PostCode], ISNULL(addrLn.value('(AddressLine1/text())[1]', 'nvarchar(max)'),'') AS [AddressLine1], ISNULL(addrLn.value('(AddressLine2/text())[1]', 'nvarchar(max)'),'') AS [AddressLine2] FROM @XMLDocument.nodes('People/Person') AS A(prs) OUTER APPLY prs.nodes('HomeInformation/Address') B(addr) OUTER APPLY addr.nodes('AddressLines') C(addrLn);

Vous pourriez lire cette réponse pour find, pourquoi ( (.../text())[1] ) est mieux que simple ...[1] …