I tried the below code to get the value of a particular node, but while loading the xml this exception is thrown:
Exception:
Data at the root level is invalid. Line 1, position 1.
XML
<?xml version="1.0"?>
<Data xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Date>11-07-2013</Date>
<Start_Time>PM 01:37:11</Start_Time>
<End_Time>PM 01:37:14</End_Time>
<Total_Time>00:00:03</Total_Time>
<Interval_Time/>
<Worked_Time>00:00:03</Worked_Time>
<Short_Fall>08:29:57</Short_Fall>
<Gain_Time>00:00:00</Gain_Time>
</Data>
C#:
XmlDocument xml = new XmlDocument();
filePath = @"D:\Work_Time_Calculator\10-07-2013.xml";
xml.LoadXml(filePath); // Exception occurs here
XmlNode node = xml.SelectSingleNode("/Data[@*]/Short_Fall");
string id = node["Short_Fall"].InnerText;
Modified Code
C#:
XmlDocument xml = new XmlDocument();
filePath = @"D:\Work_Time_Calculator\10-07-2013.xml";
xml.Load(filePath);
XmlNode node = xml.SelectSingleNode("/Data[@*]/Short_Fall");
string id = node["Short_Fall"].InnerText; // Exception occurs here ("Object reference not set to an instance of an object.")
XmlDocument d = new XmlDocument();
d.Load(@"D:\Work_Time_Calculator\10-07-2013.xml");
XmlNodeList n = d.GetElementsByTagName("Short_Fall");
if(n != null) {
Console.WriteLine(n[0].InnerText); //Will output '08:29:57'
}
or you could wrap in foreach loop to print each value
XmlDocument d = new XmlDocument();
d.Load(@"D:\Work_Time_Calculator\10-07-2013.xml");
XmlNodeList n = d.GetElementsByTagName("Short_Fall");
if(n != null) {
foreach(XmlNode curr in n) {
Console.WriteLine(curr.InnerText);
}
}
These posts helped me get past a couple of issues I had creating a CLR Stored Procedure with Restful API call against Infor M3 API.
The XML Result from these API's look like this for my code below:
miResult xmlns="http://lawson.com/m3/miaccess">
<Program>MMS200MI</Program>
<Transaction>Get</Transaction>
<Metadata>...</Metadata>
<MIRecord>
<RowIndex>0</RowIndex>
<NameValue>
<Name>STAT</Name>
<Value>20</Value>
</NameValue>
<NameValue>
<Name>ITNO</Name>
<Value>ITEM123</Value>
</NameValue>
<NameValue>
<Name>ITDS</Name>
<Value>ITEM DESCRIPTION 123 </Value>
</NameValue>
...
The CLR C# Code to accomplish listing out the Resultset from the API works as shown below:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallM3API_Test1()
{
SqlPipe pipe_msg = SqlContext.Pipe;
try
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://M3Server.domain.com:12345/m3api-rest/execute/MMS200MI/Get?ITNO=ITEM123");
request.Method = "Get";
request.ContentLength = 0;
request.Credentials = new NetworkCredential("[email protected]", "MyPassword");
request.ContentType = "application/xml";
request.Accept = "application/xml";
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
using (Stream receiveStream = response.GetResponseStream())
{
using (StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8))
{
string strContent = readStream.ReadToEnd();
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(strContent);
try
{
SqlPipe pipe = SqlContext.Pipe;
//Define Output Columns and Max Length of each Column in the Resultset
SqlMetaData[] cols = new SqlMetaData[2];
cols[0] = new SqlMetaData("Name", SqlDbType.NVarChar, 50);
cols[1] = new SqlMetaData("Value", SqlDbType.NVarChar, 120);
SqlDataRecord record = new SqlDataRecord(cols);
pipe.SendResultsStart(record);
XmlNodeList nodeList = xdoc.GetElementsByTagName("NameValue");
//List ALL Output Names + Values
foreach (XmlNode nodeRes in nodeList)
{
record.SetSqlString(0, nodeRes["Name"].InnerText);
record.SetSqlString(1, nodeRes["Value"].InnerText);
pipe.SendResultsRow(record);
}
pipe.SendResultsEnd();
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error (readStream): " + ex.Message);
}
}
}
}
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error (CallM3API_Test1): " + ex.Message);
}
}
}
Hopefully this provides helpful.
Source: Stackoverflow.com