tsql - Shredding XML Data into Tables -


I have an issue where I should convert multiple XML datasets from multiple providers to a set of pieces, in which my own The source of the table I can fix this when I am collecting top level information, but some of my information is in the archive, such as the categories my first step is to get the data out of XML and put it in some tables and then Next Data phase is to be found in my genuine table.

My XML data looks like this:

I want to split this data into a table with product ID, item ID, and category ID, when multiple categories can be assigned to a single product

< P> First of all, my database has my datatination table 

  Make Table [DBO]. [Databas]] ([id] [int] identity (1,1) no tap, [provider] [varchar] (50) no zero, [xmlcolumn] [varchar] (100), zero not [datatype] [varchar] (100) No NULL, [DestinationColumn] [varchar] (100) No NULL)

I have these records in this table: 

  88 Categories product_id int provider Product Categories 89 Categories item_id int itemId 93 Categories Categories / APIINET range information / ID int range ID, I have a SQL statement that my providers and shreds data a talc The data dictionary pulls in. Insert #SQLWork (SQL) Value ('Drop Table TempCategories') #SQLWork INSERT (SQL) Values ​​(' Selection) ') Enter #SQLWork (SQL)' 'Destination Column +' ',' Select 'categories as' Additional' from DataDown = '.' '(' '+ + Xml Column +' [1] '' + ',' '+ Datatype +' ') =' Categories' update #SQLWork set SQL = REPLACE (SQL, ',', '') where row_id = @@ include the insert #SQLWork (SQL) values ​​('TempCategories' ) included in #SQLWork (SQL) values ​​('Data' nodes applied from 'RawDetails cross' ('' '+' // ArrayOfApiNewItemDetailInfo / ApiNewItemDetailInfo '+' ') (Categories) as NewTable') @SQL nvarchar (max) selection @ SQL = insert (@SQL + announcement '', '')  
 <: + # SQLWork by command Row_Id - label @ SQL executive sp_executesql to Sql table #Sqlwork  
 < P> SQL statement that looks like this is generated @sql drop code> drop table tempcategories selection categories.value ('product_id [1]', 'int') ProviderProductId, ('ITEM_ID [1] as categories.value ] ',' Int ') Itemid, Categories.value (' categories / ApiNavCategoryInfo CategoryId TempCategories FR

As I run my statement, I get an error:

XQuery [RawDetails.Data.value ()]: 'value ()' requires singleton (or empty sequence), which 'Xdt: untypedAtomic *' type of operand has been found

Any ideas?

Thank you.

I found out that my path was wrong. 

  has changed   
 
   
  
  ('// ArrayOfApiNewItemDetailInfo / ApiNewItemDetailInfo') <<> code  
 
 and with it I changed my records 
 
   88 Categories ../ ../product_id int ProviderProductId 89 Categories ../../item_id int ItemId 93 Categories ID int class id  
 
 And that worked.
Milliliters>

Comments

Post a Comment