XML & XPath Expression

When we have an application which is having XML Files as the data storage and need the fastest way to only read the data, XPath will be a good solution.

How to loop the data from an xml into XPath is quite simple.
I will be using .Net code below to do this.

XPathDocument xpDoc = new XPathDocument(@"C:\Temp\data.xml");
XPathNavigator xpNav = xpDoc.CreateNavigator();
XPathNodeIterator xpIter = xpNav.Select("/root/row");
while (xpIter.MoveNext())
{
Console.WriteLine(xpIter.Current.GetAttribute("id", string.Empty));
}

The code will simply load the data.xml and then loop the data and write the ID to the console.

There will be occasions where we need to perform more complex queries to the data which we can do it quite simple such as in the SQL statement below:

Select * From data
Where colA In ('AA', 'ZZ')
And colB = 'X'
And colC Like '%123%'


We can achieve the same result with XPath by using this expression below :

XPathNodeIterator xpIter = xpNav.Select("/root/row[(@colA = 'AA' or @colA = 'ZZ') and @colB = 'X' and contains(@colC, '123')]");

There is a lot of ways that we can use in XPath, you can find the complete references in http://www.w3.org/TR/xpath

XML is always an interesting type of data that we can use, there is a lot of database technologies nowadays which has actually supports XML type of data such as SQL2005 where I heard we can set the indexes in the XML type of field ;)

Stored Procedure Parameters - String limitation

In some scenario, we need to pass long string to the stored procedure.
My case is that we need to pass selected checkbox options value to the stored procedure.
Sometimes when the list of checkbox is very long, the parameter string value can exceed varchar(8000 characters).

Example of the parameter : '(''chkvalue1'',''chkvalue2'',''chkvalue3''.......)'

My solution :
1. Instead of constructing the values for the query, we change the strategy to use xml string format for the parameter.
Example of the new parameter : '<root><param value="chkvalue1" /><param value="chkvalue2" /><param value="chkvalue3" />.....</root>'
2. Change the data type for the parameter from varchar(8000) to text
3. Use OPENXML to get the values from the xml :
SELECT value
INTO ##temptable
FROM OPENXML (@idoc, '/root/param',1)
WITH (value varchar(100))
4. Use this temp table to achieve the same result