SQL XML Insert multiple elements

Question

Please help! I have a field of xml within a database. I am trying to insert a new element for each element in the field. E.g:

<some-element>
<a> </a>
<b> </b>
</some-element>
<some-element>
<a> </a>
<b> </b>
</some-element>

would insert element c and go to:

<some-element>
<a> </a>
<b> </b>
<c> </c>
</some-element>
<some-element>
<a> </a>
<b> </b>
<c> </c>
</some-element>

I know I can use @fieldname.modify('insert @CElement into (/some-element)[1]') but this only changes the first element! I want to repeat this for all elements.

Any help would be very much appreciated! (I am using SQL server 2008)

PS: This is a more specific representation:

I am describing a simple table of data within the xml. I am trying to add a new column. I can add the column within the column descriptions but I need to add the column element to all the rows.

(It would not be a simple task to change the structure and I would like to avoid this!)

E.g:

<Table>
  <Columns>
    <Column ID="0">
      <Column-Name>0NAME</Column-Name>
    </Column>
    <Column ID="1">
      <Column-Name>1NAME</Column-Name>
    </Column>
    <Column ID="2">
      <Column-Name>2NAME</Column-Name>
    </Column>
    <Column ID="3">
      <Column-Name>!!!! THIS COLUMN IS BEING ADDED !!!!!</Column-Name>
    </Column>
  </Columns>
  <Rows>
    <Row ID="0">
      <C ID="0">0 contents here</C>
      <C ID="1">0 contents here</C>
      <C ID="2">0 contents here</C>
      <!-- NEW COLUMN NEEDS TO BE CREATED HERE -->
     </Row>
    <Row ID="1">
      <C ID="0">1 contents here</C>
      <C ID="1">1 contents here</C>
      <C ID="2">1 contents here</C>
      <!-- NEW COLUMN NEEDS TO BE CREATED HERE -->
    </Row>
    <Row ID="2">
      <C ID="0">2 contents here</C>
      <C ID="1">2 contents here</C>
      <C ID="2">2 contents here</C>
      <!-- NEW COLUMN NEEDS TO BE CREATED HERE -->
    </Row>

Answer 1

I know of no direct way to do this.  But the following script will do what you want.  A couple points.  this creates the standard empty element node (<C /> rather than <C></C>).  If you want the closing tag, I think you will need to add a space, the commented out code.  I added a safety variable so that it won't run forever.  You can try without it or you can set the safety to an even larger number.

DECLARE @x XML;
SET @x = '<Table>
  <Columns>
    <Column ID="0">
      <Column-Name>0NAME</Column-Name>
    </Column>
    <Column ID="1">
      <Column-Name>1NAME</Column-Name>
    </Column>
    <Column ID="2">
      <Column-Name>2NAME</Column-Name>
    </Column>
  </Columns>
  <Rows>
    <Row ID="0">
      <C ID="0">0 contents here</C>
      <C ID="1">0 contents here</C>
      <C ID="2">0 contents here</C>
     </Row>
    <Row ID="1">
      <C ID="0">1 contents here</C>
      <C ID="1">1 contents here</C>
      <C ID="2">1 contents here</C>
    </Row>
    <Row ID="2">
      <C ID="0">2 contents here</C>
      <C ID="1">2 contents here</C>
      <C ID="2">2 contents here</C>
    </Row>
    </Rows>
    </Table>';
    
    
 DECLARE @ColNum INT;
 DECLARE @ColName VARCHAR(100);
 DECLARE @nd XML;
 DECLARE @safety INT;
 
 
 SELECT @ColName = 'New Column to add', @ColNum = 3;
 
 -- Add column definition
 SET @nd = (SELECT @ColNum AS [@ID]
	, @ColName AS [Column-Name]
	FOR XML PATH ('Column'));
 
 SET @x.modify ('insert sql:variable("@nd") as last into
 (/Table/Columns[not (Column/@ID=sql:variable("@ColNum")) ])[1]
 ' );
 
 SELECT @x; 
 
 SET @safety = 0; 
 
 WHILE @safety < 100 
	AND @x.exist 
	  ('/Table/Rows/Row[ not (C/@ID=sql:variable("@ColNum"))]') = 1
 BEGIN
 SET @nd = (SELECT @ColNum AS [@ID]
   -- , ' ' AS [text()]
	FOR XML PATH ('C'));
	SET @x.modify ('insert sql:variable("@nd") as last into
 (/Table/Rows/Row[not (C/@ID=sql:variable("@ColNum")) ])[1]
 ' ); 	
 SET @safety = @safety + 1;
 END 
SELECT @x;

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

This is my idea how to do it:

declare @x xml =N'
<some-element>
	<a>5</a>
	<b>1</b>
</some-element>
<some-element>
	<a> </a>
	<b> </b>
</some-element>'

select @x.query('for $c in /some-element
                     return
                      <some-element>
							{for $f in $c//a return <a>{data($f)}</a>}
							{for $f in $c//b return <b>{data($f)}</b>}
							{for $f in $c return <c />}							
                      </some-element>')