x

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
more ▼

asked Jul 01, 2010 at 03:37 PM in Default

mattmackay76 gravatar image

mattmackay76
1 1 1 1

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.
Jul 01, 2010 at 04:14 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 = '
<products> 
<product id="123"/>
<product id="222"/>  
<product id="456"/> 
</products>';

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:

<products>
  <product id="123" name="Product abc">
    <productinfo Info="Has a good warranty" />
    <productinfo Info="Comes in the colour blue" />
  </product>
  <product id="456" name="Product foobar">
    <productinfo Info="Is a favourite of many people" />
    <productinfo Info="Will be discontinued soon" />
  </product>
</products>

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
more ▼

answered Jul 01, 2010 at 09:46 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x344
x150

asked: Jul 01, 2010 at 03:37 PM

Seen: 1954 times

Last Updated: Jul 01, 2010 at 03:37 PM