Query XML with Namespaces using XPathNavigator

Querying XML data which has namespace qualifications can be a little confusing at times, consider the following document;

<query xmlns="http://platinumdogs.com/schema/reporting/query">
 <applicationid value=" CA6E1CAE-A74F-4542-9391-82DF90301B28" />
 <prf:mdschemaid value=" 25c36533-6e4b-4034-921c-bcc7a18e1ac5" xmlns:prf="http://platinumdogs.com/schema/metadata/query" />
 <mdschemaversion value=" 1" />
 <pagesize value=" 10" />
 <numberofpagesrequired value=" 1" />
 <pageindex value=" 1" />
 <startrow value=" 1" />
 <enablepagedaccess value=" True" />
 <ydimension value=" AGE_BAND_10_YEAR" />
 <xdimension value=" AGE_BAND_05_YEAR" />
 <xdimension_limit value=" 0" />
 <fact_measure value=" NUMBER_OF_DEATHS" />
</query>

This example is declaring a default namespace qualification at the root of the document, which scopes the inner part of the document to the default namespace, which in this case is http://platinumdogs.com/schema/reporting/query.

Given this example you might expect this code to return the <applicationid> node.

	var xnQuery = new XPathDocument(new XmlTextReader(new StringReader(myXmlString))).CreateNavigator();
	var xnExpressions = xnQuery.Select("/query/applicationid");
	Console.WriteLine("Node Count = " + xnExpressions.Count.ToString());

But it doesn’t return anything at all because we haven’t told the the XPathNavigator about the namespace used in the XML document. To do that we need to create an XmlNamespaceManager object, add the namespace declaration to it and provide this to the XPathNavigator.Select method, as shown below.

	var xnQuery = new XPathDocument(new XmlTextReader(new StringReader(myXmlString))).CreateNavigator();

	var ns = new XmlNamespaceManager(xnQuery.NameTable);
	ns.AddNamespace("ns0", "http://platinumdogs.com/schema/reporting/query");

	var xnExpressions = xnQuery.Select("/ns0:query/ns0:applicationid", ns);
	Console.WriteLine("Node Count = " + xnExpressions.Count.ToString());

Notice that I’m using the namespace prefix "ns0" in my XPath query, while the XML document declares the default namespace. This doesn’t matter because the important part is the Namespace URI (“http://platinumdogs.com/schema/reporting/query”) itself, the prefix part (whether explicit or the default) is effectivly a key or alias.

If the document had been declared using an explicit namespace declaration, say xmlns:ns="http://platinumdogs.com/schema/reporting/query", I could still use the "ns0" prefix in my XPath query.

So the Namespace prefix doesn’t matter, but doesn’t the XPathDocument know all about the NamespaceURI’s declared in the document? It does, but you still need to provide the list of NamespaceURI’s to the XPath query processor because it’s of relevance to the XPath query you specify in your call to the XPathNavigator.Select method. 

You can create an XmlNamespaceManager object and populate it using the Namespace declarations found in a XPathDocument. This is usefull when you don’t know what NamespaceURI’s you’re going to come across, the following code snippet shows how to do this.

	var xnQuery = new XPathDocument(new XmlTextReader(new StringReader(myXmlString))).CreateNavigator();

	var ns = new XmlNamespaceManager(xnQuery.NameTable);
	var nodes = xnQuery.Select("//*");

	while (nodes.MoveNext())
	{
		var nsis = nodes.Current.GetNamespacesInScope(XmlNamespaceScope.Local);
		foreach (var nsi in nsis)
		{
			var prf = nsi.Key == string.Empty ? "global" : nsi.Key;
			ns.AddNamespace(prf, nsi.Value);
			Console.WriteLine("Adding... prefix=" + prf + ", uri="+nsi.Value);
		}
	}

	var xnExpressions = xnQuery.Select("/global:query/global:applicationid", ns);
	Console.WriteLine("Node Count = " + xnExpressions.Count);

	xnExpressions = xnQuery.Select("/global:query/prf:mdschemaid", ns);
	Console.WriteLine("Node Count = " + xnExpressions.Count);

The code queries the document for all nodes, iterates over those nodes, gets the Namespaces in Scope and adds the declaration to the XmlNamespaceManager.  Notice how the default namespace is handled by substituting "global" for the default namespace prefix which is an empty string.

Using XMLSchema dateTime formatted DateTime’s in .NET

This is here mostly for my benefit as I keep forgetting it, but this stuff is all over the internet.

Consuming and producing xmlschema dateTime formatted values is straightforward enough when you know the specification (see the link), briefly it is;

YYYY-MM-DDThh:mm:ss[.fff][zzzz]

The .NET class XmlConvert can be used to convert dateTime values between string and DateTime, using the ToString() and ToDateTime() methods.

   // convert from string to datetime
   var dt = XmlConvert.ToDateTime(dateString, XmlDateTimeSerializationMode.Utc);
   dt = XmlConvert.ToDateTime(dateString, XmlDateTimeSerializationMode.Unspecified);
   dt = XmlConvert.ToDateTime(dateString, XmlDateTimeSerializationMode.RoundtripKind);
   dt = XmlConvert.ToDateTime(dateString, XmlDateTimeSerializationMode.Local);

   // convert from datetime to string
   var dtStr = XmlConvert.ToString(dt, XmlDateTimeSerializationMode.Utc)
   dtStr = XmlConvert.ToString(dt, XmlDateTimeSerializationMode.Unspecified)
   dtStr = XmlConvert.ToString(dt, XmlDateTimeSerializationMode.RoundtripKind)
   dtStr = XmlConvert.ToString(dt, XmlDateTimeSerializationMode.Local)

Export SQL Server 2005 Xml Column Data to Xml File

Xml data stored using the SQL Server 2005 xml data type can be exported a number of ways and you can obviously write a managed stored procedure to do it.

However for a quick, one off, type approach you can use the BCP command.

bcp "SELECT [xml column name] FROM [TableName] FOR XML RAW"
      queryout c:\myOutputData.xml -SServerName -T -w -r -t

The downside of this is that, given the command above you’ll get your xml row data exported as shown;

<row><data>{row 1 xml}</row></data>
<row><data>{row 2 xml}</row></data>

so you might need to do some mangling on the output file, or you can obviously change the initial SELECT statement to shape your output appropriately.