asked
How to join a table to xml, pull in records and update the original XML with new elements?
So, say I have an xml field with the following blob of xml in it and lets say that I have a product info table that has more information about products such as table ProductInfo ProductID, Info ________________________________ 123, "Has a good warranty" 123, "Comes in the color blue" 456, "Is a favorite of many people" 456, "Will be discontinued soon" So, how could I join that table in and produce the following Has a good warranty Comes in the color blue Is a favorite of many people Will be discontinued soon furthermore, it would be also cool to pull in some attributes from the product table itself ProductTable ID, Name 123, "Product abc" 456, "Product foobar" and then finally produce this Has a good warranty Comes in the color blue Is a favorite of many people Will be discontinued soon Any thoughts / suggestions are much appreciated and welcome! thanks, -Matt
From what you describe, you are asking to produce invalid xml. This is because your product node refers to different things. On one hand, you want to have a product node (one per record), on the other hand you have productinfo table which might have multiple records per product. Thus you should probably opt to name the node containing product info different (for example name it productinfo, without the space). Your xml will then look like this: Has a good warrantyComes in the color blue. If this is acceptable, I can answer your question a little bit later. Please let me know.
Here is an example which might help: use AdventureWorks; go -- create product table create table dbo.Product ( id int not null primary key clustered, [name] varchar(50) not null ); go -- create productinfo table which will -- have 0 to many records per product create table dbo.ProductInfo ( ProductID int not null, info varchar(50) not null ); go -- insert some records to test different scenarios insert into dbo.Product values (123, 'Product abc'); insert into dbo.Product values (234, 'Product bcd'); insert into dbo.Product values (345, 'Product cde'); insert into dbo.Product values (456, 'Product foobar'); insert into dbo.Product values (567, 'Product efg'); -- insert records for some but not all products insert into dbo.ProductInfo values (123, 'Has a good warranty'); insert into dbo.ProductInfo values (123, 'Comes in the colour blue'); insert into dbo.ProductInfo values (234, 'Just one record'); insert into dbo.ProductInfo values (456, 'Is a favourite of many people'); insert into dbo.ProductInfo values (456, 'Will be discontinued soon'); -- example to select data as xml select
product.id, product.name,
productinfo.Info from dbo.Product product left join dbo.ProductInfo productinfo on
product.id = productinfo.ProductID order by 1 for xml auto, root('products'); -- the xml below has 3 nodes only 2 of which can find a match -- in the product table. Lets return xml populated with more -- info for every match that it can find. declare @xml xml; set @xml = ' '; select
product.id, product.name,
productinfo.Info from ( select x.item.value('@id', 'int') the_id from @xml.nodes('*/product') x(item) ) x inner join dbo.Product product on x.the_id =
product.id left join dbo.ProductInfo productinfo on
product.id = productinfo.ProductID order by 1 for xml auto, root('products'); This will result in the following xml shape: While this is not exactly the shape you requested, it is a good start. The mix of values and attributes in the final result typically requires restating the select statement for xml explicit. Please let me know if you want me to adjust the shape. Oleg
