none
Insert XML node into multiple parent nodes in multiple XML documents

    Question

  • Hi

    I'm attempting to insert a single node into multiple parent nodes using the values in that parent node to determine the value of the new node's value.

     

    I have an origional XML document:

    <Investment>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>Investment Solutions</InvestmentAdministratorName>
    		<InvestmentPortfolioName>Banker</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>ABSA</InvestmentAdministratorName>
    		<InvestmentPortfolioName>AA Life</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    </Investment>
    

    Now I generate a unique number for each administrator & portfolio for all of my documents in the database

    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('InvestmentAdministratorName[1]', 'varchar(max)') as Administrator,
    ref.value('InvestmentPortfolioName[1]', 'varchar(max)') as Portfolio
    from
    @origional
    cross apply Document.nodes('//Investment/InvestmentPortfolio') R(ref)
    
    
    Now I have a result set with the unique ID for each administrator & portfolio

    AdministratorPortfolioId	Administrator	     Portfolio
    1	            ABSA	         AA Life
    2	            Investment Solutions   Banker

    I'm now trying to update the XML document, inserting a new node (InvestmentAdministratorPortfolioId) into each InvestmentPortfolio node containing the value for the administrator & portfolio as selected from my temp table

    update @origional
    set Document.modify('insert (<InvestmentAdministratorPortfolioId>{sql:column("p.AdministratorPortfolioId")}</InvestmentAdministratorPortfolioId>) 
     as first into (/Investment/InvestmentPortfolio)[1]')
    from
    @origional
    cross apply Document.nodes('//Investment/InvestmentPortfolio') R(ref)
    join @portfolios p on p.Administrator = ref.value('InvestmentAdministratorName[1]', 'varchar(max)')
    	and p.Portfolio = ref.value('InvestmentPortfolioName[1]', 'varchar(max)')
    
    

     

    Now my XML document look as follow:

    <Investment>
     <InvestmentPortfolio>
      <InvestmentAdministratorPortfolioId>2</InvestmentAdministratorPortfolioId>
      <InvestmentAdministratorName>Investment Solutions</InvestmentAdministratorName>
      <InvestmentPortfolioName>Banker</InvestmentPortfolioName>
     </InvestmentPortfolio>
     <InvestmentPortfolio>
      <InvestmentAdministratorName>ABSA</InvestmentAdministratorName>
      <InvestmentPortfolioName>AA Life</InvestmentPortfolioName>
     </InvestmentPortfolio>
    </Investment>
    
    As you can see the new node have been correctly added to the InvestmentPortfolio node but only for the first InvestmentPortfolio node. 

    What can I do so that both instances of the InvestmentPortfolio nodes are updated?

    Thanks,

    Jacques

    • Moved by Ai-hua Qiu Tuesday, February 01, 2011 2:55 AM (From:Transact-SQL)
    Monday, January 31, 2011 10:45 AM

Answers

  • Try this solution.  It does use a a loop which is very similar to a cursor but should solve your problem.

    I've also altered the XQuery slightly because the target for an update must be one node.  So we get round this by saying "get me the first node which does not have a PortfolioId", then loop until there aren't any more.  I've also used the exist method of the XML data type instead of the value method which is preferred in joins.

    DECLARE @origional TABLE ( Document XML )
    
    INSERT INTO @origional
    SELECT 
    '<Investment>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>Investment Solutions</InvestmentAdministratorName>
    		<InvestmentPortfolioName>Banker</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>ABSA</InvestmentAdministratorName>
    		<InvestmentPortfolioName>AA Life</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    </Investment>'
    
    
    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('InvestmentAdministratorName[1]', 'varchar(max)') as Administrator,
    	ref.value('InvestmentPortfolioName[1]', 'varchar(max)') as Portfolio
    from @origional
    	cross apply Document.nodes('//Investment/InvestmentPortfolio') R(ref)
    
    
    SELECT 'before' s, *
    FROM @origional
    
    DECLARE @id INT
    
    SET @id = 0
    
    WHILE EXISTS ( SELECT * FROM @origional WHERE Document.exist('/Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)]') = 1 )
    BEGIN
    
    	UPDATE @origional
    	SET Document.modify('insert (<InvestmentAdministratorPortfolioId>{sql:column("p.AdministratorPortfolioId")}</InvestmentAdministratorPortfolioId>) 
    	 as first into (/Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)])[1]')
    	FROM @origional
    	CROSS APPLY Document.nodes('//Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)]') R(ref)
    		CROSS JOIN @portfolios p 
    	WHERE ref.exist('InvestmentAdministratorName[.=sql:column("p.Administrator")]')	= 1
    	 AND ref.exist('InvestmentPortfolioName[.=sql:column("p.Portfolio")]')	= 1
    
    	SET @id = @id + 1
    	
    	IF @id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @id ) BREAK END
    
    END
    
    SELECT @id loops
    
    SELECT 'after' s, *
    FROM @origional
    
    • Marked as answer by KJian_ Tuesday, February 08, 2011 9:32 AM
    Tuesday, February 01, 2011 11:41 AM

All replies

  • Hi,

    Because you specified the first InvestmentPortfolio node in your statements as follows:
    as first into (/Investment/InvestmentPortfolio) [1]

    I recommend that you use PATH mode to generate XML from a SELECT query, please refer to the following statements which can get the result as you expected. Please see:

    SELECT P.AdministratorPortfolioId,
    ref.value('InvestmentAdministratorName[1]', 'varchar(max)') AS InvestmentAdministratorName,
    ref.value('InvestmentPortfolioName[1]', 'varchar(max)') AS InvestmentPortfolioName
    FROM @origional CROSS APPLY Document.nodes('//Investment/InvestmentPortfolio') R(ref)
             INNER JOIN @portfolios P ON ref.value('InvestmentAdministratorName[1]', 'varchar(max)')=P.Administrator
                         AND ref.value('InvestmentPortfolioName[1]', 'varchar(max)')=P.Portfolio
     FOR XML PATH('InvestmentPortfolio'),ROOT('Investment')
    

    The result is:

    <Investment>
     <InvestmentPortfolio>
      <AdministratorPortfolioId>2</AdministratorPortfolioId>
      <InvestmentAdministratorName>Investment Solutions</InvestmentAdministratorName>
      <InvestmentPortfolioName>Banker</InvestmentPortfolioName>
     </InvestmentPortfolio>
     <InvestmentPortfolio>
      <AdministratorPortfolioId>1</AdministratorPortfolioId>
      <InvestmentAdministratorName>ABSA</InvestmentAdministratorName>
      <InvestmentPortfolioName>AA Life</InvestmentPortfolioName>
     </InvestmentPortfolio>
    </Investment>
    

    For more information, please see:
    Examples: Using PATH Mode
    http://msdn.microsoft.com/en-us/library/bb510462.aspx

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by fugitsu Tuesday, February 01, 2011 9:29 AM
    Tuesday, February 01, 2011 5:11 AM
  • Hi

    Thanks for your reply.  Given the example yes what you have given me will work.  Unfortunately the XML document I'm trying to manipulate contains thousands of other elements so it's not really going to work that well.

    I've settled for a more complex solution, one in which I determine how many InvestmentPortfolio elements there are in each document, then looping through them and updating each element.  Not the perfect solution, and I hope to get a better solution as we'll most certainly will have to do this exercise again in the future.

    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
    
    declare curResult cursor for
    	select FSRDigitalDataDocID, DigitalDataDoc, cast(cast(DigitalDataDoc.query(
    	'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";
    	count(//xbrli:xbrl/finrep-inv-ent:InvestmentsSinglePortfolio)') as varchar(10)) as int)
    	from FSRDigitalDataDocs
    	where FSRDigitalDataDocTypeID = 5
    
    declare @DocumentId int
    declare @Document xml
    declare @NodeCount int
    
    open curResult
    fetch next from curResult into @DocumentId, @Document, @NodeCount
    
    while (@@fetch_status = 0)
    begin
    	declare @index int
    	set @index = 1
    	while @index <= @NodeCount
    	begin
    		--Administrator
    		declare @administrator varchar(max)
    		set @administrator = @Document.value('
    		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[sql:variable("@index")]/finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorName)[1]', 'varchar(max)')
    		
    		
    		declare @portfolio varchar(max)
    		set @portfolio = @Document.value('
    		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[sql:variable("@index")]/finrep-inv-ent:InvestmentsSinglePortfolioPortfolioName)[1]', 'varchar(max)')
    		
    		declare @portfolioId int
    		select @portfolioId = AdministratorPortfolioId from @portfolios
    		where Administrator = @administrator and Portfolio = @portfolio
    		
    		set @Document.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>{sql:variable("@portfolioId")}</finrep-inv-ent:InvestmentsSinglePortfolioInvestmentAdministratorPortfolioId>) 
    		as first into (/xbrli:xbrl/finrep-inv-ent:InvestmentsSinglePortfolio[sql:variable("@index")])[1]')
    				
    		--print convert(varchar(max), @portfolioId) + ' ' +@administrator + ' - ' + @portfolio;
    		set @index = @index + 1
    	end
    
    	update FSRDigitalDataDocs set DigitalDataDoc = @Document where FSRDigitalDataDocID = @DocumentId
    
    
    	fetch next from curResult into @DocumentId, @Document, @NodeCount
    end
    
    
    close curResult
    deallocate curResult
    
    

    Tuesday, February 01, 2011 10:20 AM
  • Try this solution.  It does use a a loop which is very similar to a cursor but should solve your problem.

    I've also altered the XQuery slightly because the target for an update must be one node.  So we get round this by saying "get me the first node which does not have a PortfolioId", then loop until there aren't any more.  I've also used the exist method of the XML data type instead of the value method which is preferred in joins.

    DECLARE @origional TABLE ( Document XML )
    
    INSERT INTO @origional
    SELECT 
    '<Investment>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>Investment Solutions</InvestmentAdministratorName>
    		<InvestmentPortfolioName>Banker</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    	<InvestmentPortfolio>
    		<InvestmentAdministratorName>ABSA</InvestmentAdministratorName>
    		<InvestmentPortfolioName>AA Life</InvestmentPortfolioName>
    	</InvestmentPortfolio>
    </Investment>'
    
    
    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('InvestmentAdministratorName[1]', 'varchar(max)') as Administrator,
    	ref.value('InvestmentPortfolioName[1]', 'varchar(max)') as Portfolio
    from @origional
    	cross apply Document.nodes('//Investment/InvestmentPortfolio') R(ref)
    
    
    SELECT 'before' s, *
    FROM @origional
    
    DECLARE @id INT
    
    SET @id = 0
    
    WHILE EXISTS ( SELECT * FROM @origional WHERE Document.exist('/Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)]') = 1 )
    BEGIN
    
    	UPDATE @origional
    	SET Document.modify('insert (<InvestmentAdministratorPortfolioId>{sql:column("p.AdministratorPortfolioId")}</InvestmentAdministratorPortfolioId>) 
    	 as first into (/Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)])[1]')
    	FROM @origional
    	CROSS APPLY Document.nodes('//Investment/InvestmentPortfolio[not(InvestmentAdministratorPortfolioId)]') R(ref)
    		CROSS JOIN @portfolios p 
    	WHERE ref.exist('InvestmentAdministratorName[.=sql:column("p.Administrator")]')	= 1
    	 AND ref.exist('InvestmentPortfolioName[.=sql:column("p.Portfolio")]')	= 1
    
    	SET @id = @id + 1
    	
    	IF @id > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @id ) BREAK END
    
    END
    
    SELECT @id loops
    
    SELECT 'after' s, *
    FROM @origional
    
    • Marked as answer by KJian_ Tuesday, February 08, 2011 9:32 AM
    Tuesday, February 01, 2011 11:41 AM