How to get the xml node value in string

You should use .Load and not .LoadXML

MSDN Link

"The LoadXml method is for loading an XML string directly. You want to use the Load method instead."

ref : Link

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.

I had same issue and below code solved.

-> If you have xml file path

C#

XmlDocument xmlDoc = new XmlDocument();
string filePath = @"D:\Work_Time_Calculator\10-07-2013.xml";
xml.Load(filePath);
string shortFall = xmlDc.SelectSingleNode("Data/Short_Fall").InnerText;

-> If you have xml string, then below code works

C#

XmlDocument xmlDoc = new XmlDocument();
string xmlString= "your xml string";
xml.LoadXML(xmlString);
string shortFall = xmlDoc.SelectSingleNode("Data/Short_Fall").InnerText;