Hi All,

I have an xml file which looks something like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1:C4"/>
<sheetViews><sheetView tabSelected="1" workbookViewId="0"><selection activeCell="A5" sqref="A5"/></sheetView></sheetViews>
<sheetFormatPr defaultRowHeight="15"/><cols><col min="1" max="1" width="27.7109375" customWidth="1"/><col min="2" max="2" width="19.5703125" customWidth="1"/><col min="3" max="3" width="19.42578125" customWidth="1"/></cols>
<sheetData>
<row r="1" spans="1:3">
<c r="A1" s="1" t="s"><v>0</v></c><c r="B1" s="1" t="s"><v>1</v></c><c r="C1" s="1" t="s"><v>2</v></c>
</row>
</sheetData>
<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup orientation="portrait" horizontalDpi="200" verticalDpi="200" copies="0" r:id="rId1"/>
</worksheet>

From this xml file i should able to extract all the nodes from <sheetData> tag, for this i have written something like this in c#

XmlNodeList nodes = doc.SelectNodes("/worksheet/sheetData/row[@r='1']");

the above statement works well if the xml file does not have xmlns in it, but if it has that xmlns i am getting null in the nodes.

Can anyone please  help me out in getting around with this, and also if someone could give a sample to work with this using XQuery, it would be really helpful.

Thanks All,

Add XmlNamespaceManager and get nodes from it.

Refer to this old thread

http://forums.asp.net/p/1485854/3479696.aspx#3480831

And for XmlNamespaceManager

http://msdn.microsoft.com/en-us/library/d6730bwt%28VS.80%29.aspx

Or simply you can strip the name space and get nodes.

Refer

http://forums.asp.net/p/1483243/3480978.aspx

When quering XML elements that belong to a namespace, you have to create an XmlNamespaceManager, and pass it to every SelectNodes or SelectSingleNode statement you make. This object is used to resolve the namespaces in the queries, so that you can get to the correct resultset. You can't access the data without this feature. First you will have to modify your xml like this. Add "DSS" predix with your first xmlns attribute as follows.

<worksheet xmlns:DSS="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">

Then you can write the following code to get the correct result.

XmlDocument doc = new XmlDocument();

        doc.Load(Server.MapPath("XMLFile.xml"));

        XmlNamespaceManager nsMgr = new XmlNamespaceManager(doc.NameTable);

        nsMgr.AddNamespace("DSS", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

        nsMgr.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

        XmlNodeList nodes = doc.SelectNodes("//worksheet/sheetData/row[@r='1']",nsMgr);

For XQuery , please view the following link.

http://cankansu.blogspot.com/2008/11/using-xquery-in-c.html

Hope it will help.

You need to use an XmlNamespaceManager but you do not have to modify the XML to do that:

XmlDocument doc = new XmlDocument();
doc.Load("file.xml");
XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);
mgr.AddNamespace("df", doc.DocumentElement.NamespaceURI);

XmlNodeList nodes = doc.SelectNodes("/df:worksheet/df:sheetData/df:row[@r = '1']", mgr);

thank you fayaz  for your post, it helped me a lot.

this solved my issue.

thanks,

thank you Danish for the post, but I am not able to modify the xml file as it is generated dynamically.

but thank you for the post, i got to know an important thing for the future use.

Thanks,

 

I am not able to modify the xml file as it is generated dynamically.

If you do not have control over your xml file then you can load that file using XmlDocumet API.

xmlDoc.Load("c:\\myfile.xml");

And then you can tamper your xml the way you want..liek you can append nodes or attributes,delete them and so on.