How to join a table to xml, pull in records and update the original XML with new elements?

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