Writing XML in VBA

add reference to microsoft xml

Public Sub CreateXML()

Dim objDom As DOMDocument
Dim objXMLRootelement As IXMLDOMElement
Dim objXMLelement As IXMLDOMElement
Dim objXMLChildElement As IXMLDOMElement
Dim objXMLattr As IXMLDOMAttribute

Set objDom = New DOMDocument

'~~> Creates root element
Set objXMLRootelement = objDom.createElement("Entry")
objDom.appendChild objXMLRootelement

'~~> Create Time element
Set objXMLelement = objDom.createElement("Country")
objXMLelement.Text = "Sri Lanka"
objXMLRootelement.appendChild objXMLelement

'~~> Create Date element
Set objXMLelement = objDom.createElement("Date")
objXMLRootelement.appendChild objXMLelement

'~~> Creates Attribute to the Date Element and set value
Set objXMLattr = objDom.createAttribute("Value")
objXMLattr.NodeValue = "3/2/2012"
objXMLelement.setAttributeNode objXMLattr

'~~> Create Time element
Set objXMLelement = objDom.createElement("Time")
objXMLRootelement.appendChild objXMLelement

'~~> add a child element to an element
Set objXMLChildElement = objDom.createElement("Local")
objXMLChildElement.Text = "9:30"
objXMLelement.appendChild objXMLChildElement

'~~> Saves XML data to a file
objDom.Save ("C:\CSharpNoob2011.xml")
Debug.Print objDom.XML

End sub

Read XML Document into string

// using System.IO;
// using System.Xml;


XmlDocument xdoc = new XmlDocument();
xdoc.Load("C:\\Export DataSet_INPUT.xml");

// Now create StringWriter object to get data from xml document.
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
xdoc.WriteTo(xw);
String xmlstring = sw.ToString();

Manipulating XML files with Excel VBA & Xpath

Click here for more reading

Reading from XML into Excel. Sample

This is a better example. Click here

There is no syntex difference in Access and Excell VBA for manupulating XML files.

For manupulating XML through VBA you have to have MSXML.4.0 or on your system. This comes in Internet Explorer 6, if you have this installed then you need not to worry about the dll for XML. NOw you simply open your VBA application editor (i.e. Microsoft Visual Basic 6.0 editor) Goto Tools --> References,
Select Microsoft XML, v3.0 Reference from the list.

Now you are ready to code for the Xml file in VBA.



Sub Macro1()
Dim iRow As Integer
Dim oxmlDoc As MSXML2.DOMDocument
Set oxmlDoc = New MSXML2.DOMDocument
Dim oXmlNode As MSXML2.IXMLDOMNode
Dim oXmlNode1 As MSXML2.IXMLDOMNode
Dim oXmlNodes As MSXML2.IXMLDOMNodeList

'Debug.Print xmlDoc.XML
'Debug.Print xmlDoc.Text

oxmlDoc.async = False
oxmlDoc.Load ("C:\WSOutput1.xml")

Set oXmlNodes = oxmlDoc.selectNodes("//XML_OUTPUT/ITEM/ELEMENTS[@TYPE='1']") ' reading attribute
Sheets("sheet1").Activate ' Sheets(strCostData).Activate

iRow = 4
For Each oXmlNode In oXmlNodes

'Debug.Print "ID :" & oXmlNode.childNodes.item(ConsumableFields.ID).Text
'Debug.Print "Name :" & oXmlNode.childNodes.item(ConsumableFields.Name).Text
Sheets("sheet1").Range("A" & Trim(str(iRow))).Value = oXmlNode.childNodes.item(0).Text

iRow = iRow + 1
Next oXmlNode
Set xmlDoc = Nothing

End Sub

xPath Document, Navigator, Expression.

Read BookStore.xml using XPath Iterator.



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.XPath;

namespace BookStoreApp
{
class Program
{
static void Main(string[] args)
{

Program bk = new Program();

bk.DisplayBooksLess10();

bk.DisplayNovel();
bk.DisplayFirstNameAInIt();
bk.DisplayNovelOverSix();

//Add the new book
bk.AddBookNode();
}


public void DisplayFirstNameAInIt()
{
Console.WriteLine("A char in Author's first name");

XPathNavigator nav;
XPathDocument docNav;
XPathNodeIterator NodeIter;

//String strExpression;





// Open the XML.
docNav = new XPathDocument("BookStore.xml");

nav = docNav.CreateNavigator();

XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
nsmgr.AddNamespace("b", "http://www.contoso.com/books");
nsmgr.AddNamespace("g", "http://www.contoso.com/genre");
nsmgr.AddNamespace("a", "http://www.contoso.com/author");

XPathExpression expr = nav.Compile("/b:bookstore/b:book[contains(a:author/a:first-name,'a')]/b:title");
expr.SetContext(nsmgr);


//strExpression = "/bookstore/book/title[../price>10.00]";

NodeIter = nav.Select(expr);

while (NodeIter.MoveNext())
{

Console.WriteLine("Book Title: {0}", NodeIter.Current.Value);
};
Console.WriteLine("\n" + "Press ENTER key....");
Console.ReadLine();


}

public void DisplayNovelOverSix()
{
Console.WriteLine("Average of Novels over £6");

XPathNavigator nav;
XPathDocument docNav;
XPathNodeIterator NodeIter;
//String strExpression;





// Open the XML.
docNav = new XPathDocument("BookStore.xml");

nav = docNav.CreateNavigator();

XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
nsmgr.AddNamespace("b", "http://www.contoso.com/books");
nsmgr.AddNamespace("g", "http://www.contoso.com/genre");
nsmgr.AddNamespace("a", "http://www.contoso.com/author");

//XPathExpression expr = nav.Compile("/b:bookstore/b:book[@g:genre='novel' and b:price>6.00]/b:title");
//expr.SetContext(nsmgr);


string avg = nav.Evaluate("sum(descendant::b:bookstore/b:book[@g:genre='novel' and b:price>6.00]/b:price)" +
" div count(descendant::b:bookstore/b:book[@g:genre='novel' and b:price>6.00])", nsmgr).ToString();




Console.WriteLine("Books Average Price: {0}", avg);
Console.WriteLine("\n" + "Press ENTER key....");
Console.ReadLine();


}

public void DisplayNovel()
{
Console.WriteLine("Display Novels");

XPathNavigator nav;
XPathDocument docNav;
XPathNodeIterator NodeIter;
//String strExpression;





// Open the XML.
docNav = new XPathDocument("BookStore.xml");

nav = docNav.CreateNavigator();

XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
nsmgr.AddNamespace("b", "http://www.contoso.com/books");
nsmgr.AddNamespace("g", "http://www.contoso.com/genre");

XPathExpression expr = nav.Compile("/b:bookstore/b:book[@g:genre='novel']/b:title");
expr.SetContext(nsmgr);


//strExpression = "/bookstore/book/title[../price>10.00]";

NodeIter = nav.Select(expr);

while (NodeIter.MoveNext())
{

Console.WriteLine("Book Title: {0}", NodeIter.Current.Value);
};
Console.WriteLine("\n" + "Press ENTER key....");
Console.ReadLine();


}
public void DisplayBooksLess10()
{
Console.WriteLine("Books less than £10");

XPathNavigator nav;
XPathDocument docNav;
XPathNodeIterator NodeIter;
//String strExpression;





// Open the XML.
docNav = new XPathDocument("BookStore.xml");

nav = docNav.CreateNavigator();

XmlNamespaceManager nsmgr = new XmlNamespaceManager(nav.NameTable);
nsmgr.AddNamespace("b", "http://www.contoso.com/books");
nsmgr.AddNamespace("g", "http://www.contoso.com/genre");
nsmgr.AddNamespace("a", "http://www.contoso.com/author");

XPathExpression expr = nav.Compile("/b:bookstore/b:book[b:price<'10.00']/b:title");
expr.SetContext(nsmgr);


//strExpression = "/bookstore/book/title[../price>10.00]";

NodeIter = nav.Select(expr);

while (NodeIter.MoveNext())
{

Console.WriteLine("Book Title: {0}", NodeIter.Current.Value);
};
Console.WriteLine("\n" + "Press ENTER key....");
Console.ReadLine();


}

public void AddBookNode()
{

Console.WriteLine("Adding a book node to existing xml");

XmlDocument myDoc = new XmlDocument();
myDoc.Load("BookStore.xml");
XmlNode root = myDoc.DocumentElement;
XmlNamespaceManager nsmgr = new XmlNamespaceManager(myDoc.NameTable);
nsmgr.AddNamespace("b", "http://www.contoso.com/books");
nsmgr.AddNamespace("g", "http://www.contoso.com/genre");
nsmgr.AddNamespace("a", "http://www.contoso.com/author");
// Create a Book element and populate its attributes
System.Xml.XmlElement newBook = myDoc.CreateElement("book", "http://www.contoso.com/books");
//create the three attributes to hold the values
newBook.SetAttribute("genre", "http://www.contoso.com/genre", "novel");
newBook.SetAttribute("publicationdate", "2011-06-14");
newBook.SetAttribute("ISBN", "1-1245-45455");
System.Xml.XmlElement xeTitle = myDoc.CreateElement("title");
xeTitle.InnerText = "MyBook";
// Insert the new element under the node we created
newBook.AppendChild(xeTitle);
System.Xml.XmlElement myAuthor = myDoc.CreateElement("author");
myAuthor.SetAttribute("xmlns", ("http://www.contoso.com/author"));
System.Xml.XmlElement xeFirstname = myDoc.CreateElement("first-name");
xeFirstname.InnerText = "Siraj";
myAuthor.AppendChild(xeFirstname);
System.Xml.XmlElement xeLastname = myDoc.CreateElement("last-name");
xeLastname.InnerText = "Zarook";
myAuthor.AppendChild(xeLastname);
newBook.AppendChild(myAuthor);
// Price
System.Xml.XmlElement xePrice = myDoc.CreateElement("price");
xePrice.InnerText = "1.47";
// Insert the new element under the node we created
newBook.AppendChild(xePrice);
//append the whole node to file
myDoc.DocumentElement.AppendChild(newBook);
myDoc.Save("BookStoreModified.xml");

Console.WriteLine("File BookStoreModified.xml has been created in the application folder"
+ "\n" + "Press ENTER key..");

Console.ReadLine();
}

}
}

XML with asp.net - Simple work

XmlDataSource.XPath Property

Walkthrough: Creating a Web Page to Display XML Data

Read XML File

'xmldoc.Load("C:\Siraj\" & "Response_Test.xml")

'Dim Response_data As String

Dim nsmRequest As New XmlNamespaceManager(xmldoc.NameTable)

nsmRequest.AddNamespace("soapenv", "http://schemas.xmlsoap.org/soap/envelope/")
nsmRequest.AddNamespace("oug", "http://webcomponent.components.oug.osgi.scorex.com/")

'XmlElement root = xmldoc.DocumentElement;
Dim nodes As XmlNodeList = xmldoc.DocumentElement.SelectNodes("//oug:MyWSResponse/items/item", nsmRequest)
' you cannot use prefix with NameSpaceMng
'XmlNodeList nodes = xmldoc.DocumentElement.SelectNodes("//myWSResponse/items");
For Each node As XmlNode In nodes
'Response.Write(node.InnerText)
If node.Name.ToUpper = "ITEM" And node.InnerText.ToUpper = "MY_RC" Then
'
If node.NextSibling.InnerText = "0" Then
SubmissionSuccess = True
End If
ElseIf node.Name.ToUpper = "ITEM" And node.InnerText.ToUpper = "RC_DESC" Then ' useful when WF_RC IS <> 0
RC_DESC = node.NextSibling.InnerText

ElseIf node.Name.ToUpper = "ITEM" And node.InnerText.ToUpper = "CODE" Then ' useful when WF_RC IS <> 0
' This could be a lookup table - ALL SYS???? Error codes
Return_Code = node.NextSibling.InnerText
If Return_Code <> "0" Then
SubmissionSuccess = False
Exit For ' If error during submission data tag will not be used
End If
ElseIf node.Name.ToUpper = "ITEM" And node.InnerText.ToUpper = "DATA" Then
Response_Data = node.NextSibling.InnerText
End If
Next



*******Select Single Node


thisNode = xmlResponseData.DocumentElement.SelectSingleNode("//OUTPUT/MY_NUM")
If Not thisNode Is Nothing Then
AppNum = thisNode.InnerText
End If

Reading XML Files with ASP.NET

This is full article from Faizal Khan.

Reading XML Files with ASP.NET

We can bind an XML file to a list/combo box control.

Read This

Populate with ListArray and Hashtable/

Read This