Skip to main content

Working with xml data type in MS SQL server

Previously I had worked with xml data type in SQL 2005. There I was serializing the object in to xml and putting it into xml variable in SQL table. And whenever needed I would pull the xml from SQL table and create an object (deserialize). But recently I was presented with a challenge to manipulate the xml in SQL (T-SQL) itself.

In this scenario, xml text was being stored in a table as nvarchar(max). Let’s say the column name is “xmlFrag”. A function was written to return rows based on a set parameter. But for the set parameter, if the result set returns multiple rows, the function would combine the xmlFrag column (from both rows) and return one xml fragment. Below is the example.

1st row of the resultset.



  
    Gud Day Biscuits
    
      
        Cashew nuts
       
      
        Honey


2nd row of the resultset.



  
    Britannia Marie
    
      
        Wheat
      
      
        Barley



Then the function would combine these 2 rows data into one single xml fragment. The combined xml fragment would look like…



  
    Gud Day Biscuits
    
      
        Cashew nuts
       
      
        Honey
      
    
  
  
    Britannia Marie
    
      
        Wheat
      
      
        Barley



If you notice the highlighted node, the order of the feature is repeating (1,2,1,2). This was creating a problem in processing the nodes. And hence I was asked to make the order unique (1,2,3,4).

We had 2 approaches, one to make changes in the c# code itself. Two, make changes in the SQL function. Regarding making changes in the c# code, it would have been a huge task because this feature was being used in many places. So I decided to go ahead with changing the SQL function itself. Below are the steps followed to achieve the same.

Step 1. As we already know the function will return merged xml fragment from 2 rows. In the first step we will have to take the merged xml fragment into an xml variable and count the number of nodes in the xml fragment. So the total nodes are 4 in our case.

Step 2. After we know the total number of nodes where we have to make changes, we will have to loop through the nodes and modify the same. In the second step, we will have to use the .modify method on the xml variable. In this scenario, we actually have 2 variants. One, which node we are pointing to. Two, what value we want to replace. In both situations, the @counter variable will be used.

So in the modify method, the first part is “replace value of”. In here we will have to point to the correct node. This can be achieved by pointing to the node in the hierarchy (/Product/Item/Features/Feature) and then pointing to the attribute of the node (/@Order). Here since there are 4 nodes, we will have to point to the single node by specifying the position. This can be achieved by [position()=sql:variable(“@counter”)].

The second part (replacing with) we will have to specify the counter variable. This can be achieved by sql:variable(“@counter”). And of course we will have to increment the @counter variable.


Below is the complete solution. 


DECLARE @processed_xml as xml
DECLARE @counter as INT
DECLARE @node_count as INT
-- This is the previous return statement in function 
-- RETURN '' + replace(replace(@new_text_all,'',''),'','') + '' 

-- New code to add sequence number to order attribute of Feature node.
set @counter = 1;
set @processed_xml = '' + replace(replace(@new_text_all,'',''),'','') + ''
 
-- Count of Feature Nodes in xml
SELECT @node_count = @processed_xml.query(' {count(/Product/Item/Features/Feature)}').value('count[1]','int')  

WHILE @counter <= @node_count 
BEGIN
      SET @processed_xml.modify('replace value of ((/Product/Item/Features/Feature/@Order)[position()=sql:variable("@counter")][1]) 
            with sql:variable("@counter")')
      SET @counter = @counter + 1        
END
 
-- New return statement for function
RETURN cast(@processed_xml as nvarchar(max))


Challenges faced were related to using SQL variable in both replace value of clause as well as with clause. The final output would look like below.



  
    Gud Day Biscuits
    
      
        Cashew nuts
       
      
        Honey
      
    
  
  
    Britannia Marie
    
      
        Wheat
      
      
        Barley



Feel free to contact me in case you need help.
-Vighnesh Bendre
Labels:

Comments

Post a Comment

Popular posts from this blog

Create list view - Conditional Formatting in SharePoint Designer 2010

In this example, we are going to format a column based on certain condition. Here I already have a list called Projects. I also have workflow associated with it. So whenever I create a new item in the list, workflow status column shows ‘In Progress’. Subsequently when the workflow in completed, the workflow status column shows ‘Completed’. For demonstration purpose, I will set the background color of workflow status column to yellow when the status is ‘In Progress’ and to green when the status is ‘Completed’. In SharePoint Designer open the site on which you are working. Click on ‘Lists and Libraries’ link. Choose the ‘Projects’ list. In SharePoint Designer Navigation, choose ‘Lists and Libraries’. In the list settings page, click on ‘New’ in ‘Views’ section. Provide appropriate name for the view and click OK. After choosing list, click on ‘New’ in the Views section. Give appropriate name to the list. Now click on any column, then in the ribbon, click on List View Tools-&g
1 comment
Read more

SharePoint 2013 (SP 15) - Creating Custom Lists

As I am exploring SharePoint 2013, I found out that there are lot of things that are new and there are lot of thing that are old but presented in a different manner as compared to SharePoint 2010. For example, Site Actions was on the top left corner in SP 2010. But in SP 15 (SharePoint 2013) we dont have ‘Site Actions’ button. But instead we have a settings icon which is placed at top right corner. When you click on the settings icon select Add an App. This will basically allow you to add custom lists, documents libraries etc. Add an App is basically the same as More Options in SharePoint 2010. From now on custom lists & libraries will be called apps. Just like in Apple store or Android store you can develop apps for SharePoint and sell it. You can find more information here: http://officepreview.microsoft.com/en-us/store/apps-for-sharepoint-FX102804987.aspx When you click on Add an App you will be navigated to a different scree which will display different opti
1 comment
Read more

Working with large xml files in c# .net

Working with large (huge) xml files is always a pain in the … The reason? These files can’t be loaded in to memory. On my desktop, where I have 2 gigs memory, I can’t open the file in even notepad. I was presented with a challenge recently to manipulate one such large xml file. The xml file was of 550+ MB. I know many would say I have seen bigger xml files than this. But the heart of the matter is if I can’t open 550+ MB file in notepad or in xmldocument in c#, then I can’t open any file bigger than this. And hence the logic to play with these files would remain same. The scenario: We have an xml file from which we want to remove a single node without removing its children. In the below sample xml fragment, the node has to be removed. The children nodes, must then be attached to ( node’s parent) node. One Two 100.22 GoodDay 3 4 Five 200.09 Cra
3 comments
Read more