Insert XML node with value from an existing result set

See more:

XML

SQL-Server

Hi
I'm attempting to insert a node into a XML document (this I'm getting right) and the node's value should be looked up from an existing result set (which I'm not getting right).

I have a temporary table which holds a unique key for two string values and this unique key I need to insert back into the XML document.

With this query I'm extracting the string values from the existing XML documents and generating a unique key for each combination.

declare @portfolios table
(AdministratorPortfolioId int not null identity(1,1), Administrator varchar(max), Portfolio varchar(max))
insert into @portfolios (Administrator, Portfolio)
select distinct
ref.value(
'declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";
finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorName[1]', 'varchar(max)') as Administrator,
ref.value(
'declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";
finrep-inv-ent:InvestmentsSinglePortfolioPortfolioName[1]', 'varchar(max)') as Portfolio
from
FSRDigitalDataDocs
cross apply DigitalDataDoc.nodes(
'declare namespace xbrli="http://www.xbrl.org/2003/instance";
 declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";
//xbrli:xbrl/finrep-inv-ent:InvestmentsSinglePortfolio') R(ref)
where FSRDigitalDataDocTypeID = 5

Now I'm attempting to insert a new node into the XML document, getting the value from my temp table

update FSRDigitalDataDocs
set DigitalDataDoc.modify(
'declare namespace xbrli="http://www.xbrl.org/2003/instance";
 declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";
 insert (<finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorPortfolioId>'+p.AdministratorPortfolioId+'</finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorPortfolioId>) 
 as first into (/xbrli:xbrl/finrep-inv-ent:InvestmentsSinglePortfolio)[1]')
from
FSRDigitalDataDocs ddd
cross apply DigitalDataDoc.nodes(
'declare namespace xbrli="http://www.xbrl.org/2003/instance";
 declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";
//xbrli:xbrl/finrep-inv-ent:InvestmentsSinglePortfolio') R(ref)
join @portfolios p on p.Administrator = ref.value('declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorName[1]', 'varchar(max)')
	and p.Portfolio = ref.value('declare namespace finrep-inv-ent="http://www.aforbes.co.za/finrep/investments/entered/2009-07-01";finrep-inv-ent:InvestmentsSinglePortfolioPortfolioName[1]', 'varchar(max)')
where FSRDigitalDataDocTypeID = 5

This however does not work as the XML data type method "modify" needs to be a string literal. How do I get the unique value into the modify statement?

Thanks,
Jacques