Click here to Skip to main content
Click here to Skip to main content

Tagged as

XML to Query Converter

, 7 Mar 2013
Rate this:
Please Sign up or sign in to vote.
Converts XML to SQL statements and executes them.

Introduction

I would like to share something in SQL which might be useful in some circumstances. The SQL procedure which I have uploaded depends on an input parameter (XML), converts it to a query, and executes it.

Background

I once got a requirement in ASP.NET (C#) in which I had multiple GridViews with editable textboxes in each row. When the user enters a valid amount in each one of them and hits the Save button, all the valid things are saved in the database.

Now the usual solution for this (there would be a lot of solutions, but being a novice I speak in their perspective) would be to generate queries (Insert/Updates) and execute them using ADO.NET. But I thought of doing something else to resolve this in a better fashion as I am a fan of StoredProcedures rather than generating inline queries. So I developed this XmlToQuery converter which is uploaded with this post.

Using the Code

This is a simple idea of just constructing XML syntaxes (type: String in C# .NET) and passing it as a parameter to the procedure which will parse through it and form the necessary queries and execute them in one shot. Now this logic is customizable to our needs. Initially, I had built it for the purpose of constructing Update queries, but later I extended it to handle Inserts as well (so you would be seeing update based variable declarations inside..Smile | <img src=).

Using the procedure:

EXECUTE Proc_xmltoqueryconverter
'<DataSet><tableDetails>TableName,(ColA;ColB),-1,(ValueA;ValueB),-1,INSERT</tableDetails>
<tableDetails>TableName,PKFieldName,PKFieldValue,
TobeUpdatedFieldName,TobeUpdatedFieldValue,UPDATE</tableDetails>
</DataSet>'   

The main tag is the <DataSet> tag, and <tabledetails> provides the info. For individual queries, the last word in the tag represents whether it is an Insert/Update.

Update: For an Update, the syntax is:

<tableDetails>TableName,PKFieldName,PKFieldValue,TobeUpdatedFieldName,
TobeUpdatedFieldValue,UPDATE</tableDetails>

Insert: For Insert, the syntax is:

<tableDetails>TableName,(ColA;ColB),-1,(ValueA;ValueB),-1,INSERT</tableDetails>

where the column list is provided by (ColA;ColB) and the value list is provided by (ValueA;ValueB), both of which can be multiple but equal in numbers. 

Like this, we can have multiple <tabledetails> in this (pardon my namings) and we can execute these queries with atomicity and have the advantages of a stored procedure.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

BharatRamV
Software Developer (Senior) iInterChange Systems P. Ltd
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 7 Mar 2013
Article Copyright 2013 by BharatRamV
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid