MS SQL Server
  1. MS SQL Server
  3. Extraction de valeurs à partir d'une list XML à deux niveaux dans une colonne SQL
Intereting Posts
comprendre comment fonctionne une key primaire et comment l'utiliser S'assurer qu'un SProc ne renvoie pas trop de données Conception de hiérarchies de dimensions: naturelles ou non naturelles Comment choisir le classment de la database SQL Server SQL CASE vs JOIN efficacité Objet INSERT ou UPDATE s'il existe déjà en utilisant BulkCopy (C #, SQL) Pourquoi SSRS ignore-t-il datatables de certaines colonnes renvoyées par une méthode Web? Plage de dates basée sur aujourd'hui SQL Server – PIVOT – deux colonnes en rangées SQL Insérer une ligne ou plusieurs lignes de données? Comment merge des intervalles contigus dans SQL Server Utilisation d'EXEC dans une instruction SELECT dans SQL Server Perfmon, comment combiner Combine FirstValueA et FirstValueB? CAS dans la clause MSSQL WHERE – erreur odbc Délai de connection dans ODBC

Extraction de valeurs à partir d'une list XML à deux niveaux dans une colonne SQL

J'ai une database SQL Server 2008 à partir de laquelle j'extrais plusieurs valeurs de diverses tables à mettre dans une seule table. Parmi ces valeurs, il y a des données tirées de XML qui jusqu'à récemment étaient stockées sur un seul niveau comme ceci: 

<XMLData> <Item> <Name>Name1</Name> <Value>Value1</Value> </Item> <Item> <Name>Name2</Name> <Value>Value2</Value> </Item> <Item> <Name>Name3</Name> <Value>Value3</Value> </Item> <Item> <Name>Name4</Name> <Value>Value4</Value> </Item> </XMLData>

Je voudrais extraire les informations nécessaires avec la méthode suivante: 

 SELECT Name = IXML.value('(./Name)[1]', 'varchar(20)'), Value = IXML.value('(./Value)[1]', 'varchar(20)') INTO dbo.newTable FROM dbo.oldTable CROSS APPLY oldTable.InfoXML.nodes('/XMLData/item') Book(IXML)

Ce qui reviendrait: 

 Name Value -------------- Name1 Value1 Name2 Value2 Name3 Value3 Name4 Value4

Cependant, maintenant la list XML a été modifiée et est générée dans une autre list comme ceci: 

 <XMLData> <LongDirectory> <Category> <Item> <CategoryName>Cat1</CategoryName> <SubCategory> <Item> <Name>Name1</Name> <Value>Value1</Value> </Item> <Item> <Name>Name2</Name> <Value>Value2</Value> </Item> <Item> <Name>Name3</Name> <Value>Value3</Value> </Item> </SubCategory> </Item> <Item> <CategoryName>Cat2</CategoryName> <SubCategory> <Item> <Name>Name4</Name> <Value>Value4</Value> </Item> <Item> <Name>Name5</Name> <Value>Value5</Value> </Item> </SubCategory> </Item> <Item> <CategoryName>Cat3</CategoryName> <SubCategory> <Item> <Name>Name6</Name> <Value>Value6</Value> </Item> <Item> <Name>Name7</Name> <Value>Value7</Value> </Item> </SubCategory> </Item> </Category> </LongDirectory> </XMLData>

Et j'ai besoin de générer l'information pour ressembler à ceci: 

 Name Value Category ------------------------- Name1 Value1 Cat1 Name2 Value2 Cat1 Name3 Value3 Cat1 Name4 Value4 Cat2 Name5 Value5 Cat2 Name6 Value6 Cat3 Name7 Value7 Cat3

Comment pourrais-je modifier ma requête pour accommoder le changement de structure? Toute aide est appréciée.

Vous pouvez le faire avec des methods node () nestedes: 

 select ICvalue('(Name)[1]', 'varchar(20)') as Name, ICvalue('(Value)[1]', 'varchar(20)') as Value, CCvalue('(CatName)[1]', 'varchar(20)') as Category -- into dbo.newTable from dbo.oldTable as T cross apply T.InfoXML.nodes('XMLData/Category') as C(C) cross apply CCnodes('Item') as I(C)

Ou utilisez l'axe parent ( .. ): 

 select ICvalue('(Name)[1]', 'varchar(20)') as Name, ICvalue('(Value)[1]', 'varchar(20)') as Value, ICvalue('(../CatName)[1]', 'varchar(20)') as Category -- into dbo.newTable from dbo.oldTable as T cross apply T.InfoXML.nodes('XMLData/Category/Item') as I(C)

sql fiddle exemple

mettre à jour : 

 select ICvalue('(Name)[1]', 'varchar(20)') as Name, ICvalue('(Value)[1]', 'varchar(20)') as Value, CCvalue('(CategoryName)[1]', 'varchar(20)') as Category from dbo.oldTable as T outer apply T.InfoXML.nodes('XMLData/LongDirectory/Category/Item') as C(C) outer apply CCnodes('SubCategory/Item') as I(C)

sql fiddle exemple