MS SQL Server
  1. MS SQL Server
  3. Interrogation de données distinctes dans une colonne XML dans SqlServer 2005
Intereting Posts
Catégorie d'attente des ressources de SQL Server 2008 Activity Monitor: Latch inclut-il un processeur ou uniquement des E / S de disque? Créer une connection SQL pour l'user Windows sql insert dans asp.net SQL Server, requête Différence entre EF.SqlServer et EF.SqlServerCompact Suppression des loggings avec condtion Le formatting de la date SQL Server provoque le problème du type de données Le moyen le plus rapide d'insert en masse à partir d'un file text sur un server SQL sans services d'intégration Afficher combien de time une requête doit s'exécuter dans SQL Server utilisant CASE pour sélectionner la colonne pour SET dans l'instruction UPDATE IN SQL SERVER Obtenir une erreur dans SQL Server "Syntaxe incorrecte près de" GO "" lorsque je copy un script de page à partir du bloc-notes, mais pas lorsque j'écris "GO" SELECT sans ORDER non Tri sur index clusterisé SQL Server 2012 consum trop de trafic réseau pour récupérer toutes datatables Déploiement d'un assemblage CLR dans une database différente du rest des objects Requête SQL, select les valeurs d'une colonne uniquement si

Interrogation de données distinctes dans une colonne XML dans SqlServer 2005

J'ai des données XML stockées dans une colonne XML dans une table dans sql server 2005.

Record1 aurait des données pour cette colonne ressemblerait à ceci: 

<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Lion</Name> </Thing> <Thing> <Name>Shoe</Name> </Thing> </Things> </ArrayOfThings>

Record 2 pourrait avoir des données comme celle-ci pour cette colonne 

 <ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Elephant</Name> </Thing> <Thing> <Name>Hammer</Name> </Thing> <Thing> <Name>Bucket</Name> </Thing> </Things> </ArrayOfThings>

Quelqu'un peut-il m'aider avec ce que la syntaxe aurait l'air de sélectionner des choses distinctes de cette table.

Les résultats returnnés ressembleraient à ceci: Monkey Lion Shoe Elephant Hammer Bucket

évidemment ce n'est pas des données de production 🙂

Script d'installation: 

 CREATE TABLE [SomeSchema].[MyTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [ThingData] [xml] NULL, CONSTRAINT [PK_Party] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO INSERT INTO [SomeSchema].[MyTable] ([ThingData]) VALUES ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Lion</Name> </Thing> <Thing> <Name>Shoe</Name> </Thing> </Things> </ArrayOfThings> ') GO INSERT INTO [SomeSchema].[MyTable] ([ThingData]) VALUES ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Elephant</Name> </Thing> <Thing> <Name>Hammer</Name> </Thing> <Thing> <Name>Bucket</Name> </Thing> </Things> </ArrayOfThings> ') GO

Et le select irait contre la colonne dans le tableau

create table #t1(id int not null identity(1,1),ThingData xml)

insert #t1(ThingData) values ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Lion</Name> </Thing> <Thing> <Name>Shoe</Name> </Thing> </Things></ArrayOfThings>')

insert #t1(ThingData) values ( '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Elephant</Name> </Thing> <Thing> <Name>Hammer</Name> </Thing> <Thing> <Name>Bucket</Name> </Thing> </Things></ArrayOfThings>')

;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns) select DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)') from #t1 cross apply #t1.[ThingData].nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') as Array(Things)

Vous pouvez également utiliser facilement le langage XQuery embedded de SQL Server 2005 au lieu de la routine OPENXML et get le même résultat très facilement: 

 DECLARE @input XML SET @input = '<ArrayOfThings xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/SomeCompany"> <Things> <Thing> <Name>Monkey</Name> </Thing> <Thing> <Name>Elephant</Name> </Thing> <Thing> <Name>Hammer</Name> </Thing> <Thing> <Name>Bucket</Name> </Thing> </Things> </ArrayOfThings>' ;WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns) SELECT DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)') FROM @input.nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') AS Array(Things)

Vous créez essentiellement une "pseudo-table" appelée Array.Things qui contient une "ligne" pour chaque input de ce type spécifié – ici une <Thing> dans la structure donnée.

Ensuite, vous pouvez accéder à ces «rangées» et saisir les éléments individuels, ici la valeur <Name> , et vous pouvez les sélectionner et travailler avec eux.

Si utiliser

Script d'installation:
CREATE TABLE [SomeSchema]. [MyTable] (….

de la question, alors: 

 WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/SomeCompany' AS ns) select DISTINCT Array.Things.value('(ns:Name)[1]', 'varchar(50)') Name FROM [SomeSchema].[MyTable] MT CROSS APPLY MT.ThingData.nodes('/ns:ArrayOfThings/ns:Things/ns:Thing') AS Array(Things)