Have you ever come across a scenario where you need to make the key names unique in an XML document while keeping the key values the same? In this blog post, we will explore a creative solution using XQuery in SQL Server.

Let’s consider a contrived XML snippet that contains a set of key/value pairs:

One solution to make the key names unique is to use a FLWOR expression to loop over the nodes and generate a new XML structure. The idea is to navigate through each <setting> node and check if there are any other <setting> nodes with the same @name value preceding it in the document. If duplicates are found, we append a sequential number to the end of the key name to make it unique within the document.

Here’s an example of the solution:

SELECT @xml.query ('

  

    {
      for $x in /UnitInformation/section/setting
      return
        if((count(/UnitInformation/section/setting[@name=$x/@name][. << $x])) > 0)
        then
          <setting name="{data(concat($x/@name, "_", xs:string(count(/UnitInformation/section/setting[@name=$x/@name][. << $x]))))}" value="{data($x/@value)}" />
        else
          $x
    }
  
'
)

Although the code may appear complex, let’s break it down step by step. The FLWOR constructor sets up a loop over a collection of XML nodes (<setting>) and assigns each instance to a local variable $x. During each iteration, it either returns the assigned variable $x or a new XML node based on the outcome of the if condition.

The if condition checks for <setting> nodes with the same @name attribute as the current variable $x and where the position of that node precedes the current one in the document order. The << test is an XQuery order comparison test that helps determine the position of XML nodes within an XQuery.

If the count of such nodes is zero, it means no duplicates were found, and the code simply returns the variable $x (the entire <setting> node with its attributes). However, if the count is non-zero, indicating the presence of duplicates, a new <setting> node is created. The @name attribute value is constructed by concatenating the original @name value with an underscore and the number of preceding nodes with the same @name value. The @value attribute is extracted from the variable $x.

When you run the code, you will get a result where duplicate key names are made unique.

XQuery is a powerful feature in SQL Server, similar to Service Broker. Although it may seem challenging at first, once you grasp its concepts, you’ll realize its potential. While XQuery can be slower in SQL Server compared to other operations, it can be a fantastic option for quick ad hoc XML manipulation within the database, eliminating the need to send data to an application service layer for transformations.

Let’s hope that JSON follows a similar path, with a dedicated data type and its own set of methods in future SQL versions.

Thank you for reading! Feel free to follow me on Twitter @sqlserverrocks and subscribe to my blog’s RSS feed for more SQL Server insights. If you have any questions or comments, please don’t hesitate to reach out to me directly.

Happy querying!