Database Programming: Using the .modify() Method Against an XML Variable in SQL Server 2008

download CU1 for SQL Server 2008 SP1Make a note.. here’s my first useful programming tip for SQL Server 2008 (and yes, there will be more to come; I’m about a week and a half into my first SQL Server 2008 development project)..

Way back in February of aught-seven, I shared Jerry’s solution for running the .modify() method against an XML memory variable, which wasn’t possible in SQL Server 2005. Jerry built a helper procedure and used it to build and execute the XQuery.

Well, as nice as it was to have Jerry’s workaround in our quiver, imagine how wonderful I felt when SQL Server 2008 very politely dealt with the following directive:

-- add the Request node to the XML
SET @IncomingXML.modify('insert <Request UserName="{sql:variable ("@UserName")}">
</Request> as first into /IPNuggets[1])[1]')

This syntax takes XML which looks like this..

<IPNuggets>
  <IPNugget Version="6" Type="1">
    <NVarCharValue>A Value</NVarCharValue>
  </IPNugget>
  <IPNugget Version="6" Type="2">
    <NVarCharValue>Another Value</NVarCharValue>
  </IPNugget>
</IPNuggeta>

..and makes it look like this..

<IPNuggets>
  <Request UserName="DOMAIN\YourNameHere" />
<IPNugget Version="6" Type="1">
    <NVarCharValue>A Value</NVarCharValue>
  </IPNugget>
  <IPNugget Version="6" Type="2">
    <NVarCharValue>Another Value</NVarCharValue>
  </IPNugget>
</IPNuggeta>

In this case, we’re adding the Request node to the XML, as the first child of the first (and only) /IPNuggets link, and we’re assigning the contents of the T-SQL variable @UserName (in this case, “DOMAIN\YourNameHere”) to the UserName element.

As helpful as Jerry’s workaround has been, I like this way better.. kudos to the SQL Server 2008 development team for repairing this issue!

-wp


this copyrighted material was originally posted at https://blogs.technet.com/wardpond

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.