Transact sql (2012) Using @xml.modify to insert 'elements using a variable

Question

I need to add an element using a variable

This works --

declare @XML xml = '
<DWDocument>
  <FileInfos>
    <ImageInfos>
      <ImageInfo id="0,0,0" nPages="0">
        <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
      </ImageInfo>
      <ImageInfo id="1,0,0" nPages="0">
        <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
      </ImageInfo>
    </ImageInfos>
  </FileInfos>
</DWDocument>
'

SET @xml.modify ('insert <ImageInfo id="2,0,0" nPages="0"></ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

Gives me:

<ImageInfo id="2,0,0" nPages="0" /><ImageInfo>

BUT...

I need the '2' to be a variable

I tried:

declare @XML xml = '
<DWDocument>
  <FileInfos>
    <ImageInfos>
      <ImageInfo id="0,0,0" nPages="0">
        <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
      </ImageInfo>
      <ImageInfo id="1,0,0" nPages="0">
        <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
      </ImageInfo>
    </ImageInfos>
  </FileInfos>
</DWDocument>
'

declare @ImageInfo_ID nvarchar(50) = '"2,0,0"'
declare @ImageInfo_nPages nvarchar(50) = '"0"'

SET @xml.modify ('insert <ImageInfo id={sql:variable("@ImageInfo_ID")}></ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

gives me the error:

Msg 2225, Level 16, State 1, Line 46
XQuery [modify()]: A string literal was expected

SET @xml.modify ('insert <ImageInfo id{sql:variable("@ImageInfo_ID")}></ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

gives me the error

Msg 2205, Level 16, State 1, Line 48
XQuery [modify()]: "=" was expected.

SET @xml.modify ('insert <ImageInfo>{sql:variable("@ImageInfo_ID")}</ImageInfo>
         as last into (DWDocument/FileInfos/ImageInfos)[1]')

has no error but the data is wrong, gives me

<ImageInfo>"2,0,0" nPages="0"</ImageInfo>

the 'id=' is missing

How do I specify the attribute name? id=@ImageInfo_ID?

What I REALLY NEED is

<ImageInfo id=@ImageInfo_ID nPages=@ImageInfo_nPages></ImageInfo>

to get to

<ImageInfo id="2,0,0" nPages="0"></ImageInfo>

What am I missing?

Answer 1

Please try the following solution.

It is using XQuery FLWOR expression to compose needed XML.

SQL

DECLARE @xml XML = 
N'<DWDocument>
    <FileInfos>
        <ImageInfos>
            <ImageInfo id="0,0,0" nPages="0">
                <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0"/>
            </ImageInfo>
            <ImageInfo id="1,0,0" nPages="0">
                <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0"/>
            </ImageInfo>
        </ImageInfos>
    </FileInfos>
</DWDocument>';

DECLARE @ImageInfo_ID NVARCHAR(50) = '2,0,0';
DECLARE @ImageInfo_nPages NVARCHAR(50) = '0';

SET @xml = @xml.query('<DWDocument><FileInfos><ImageInfos>
{
    for $x in //DWDocument/FileInfos/ImageInfos
    return if ($x[position() lt last()]) then $x
        else $x, <ImageInfo id="{sql:variable("@ImageInfo_ID")}" nPages="{sql:variable("@ImageInfo_nPages")}"></ImageInfo>
}
</ImageInfos></FileInfos></DWDocument>');

-- test
SELECT @xml;

Output

<DWDocument>
  <FileInfos>
    <ImageInfos>
      <ImageInfos>
        <ImageInfo id="0,0,0" nPages="0">
          <FileInfo fileName="9b7b36ac-c705-49ba-bf91-a952bdb44576.eml" dwFileName="f0.eml" type="normal" length="0" />
        </ImageInfo>
        <ImageInfo id="1,0,0" nPages="0">
          <FileInfo fileName="Response to Deficiency Letter.docx" dwFileName="f1.docx" type="normal" length="0" />
        </ImageInfo>
      </ImageInfos>
      <ImageInfo id="2,0,0" nPages="0" />
    </ImageInfos>
  </FileInfos>
</DWDocument>