Read specific node in XML and pass to Stored Procedure in sql server

Question

I have an XML file, in which I want to extract data from the node whose name will be passed dynamically. Once the node name is received, I want to extract the data of the nodes with the node name and pass it to the Stored Procedure. Following is my code. I have never done this so please can you explain how to go about it.

string yourpath = Environment.CurrentDirectory + @"\DataItemTables.xml";
            System.Xml.Linq.XDocument myxml = System.Xml.Linq.XDocument.Load(yourpath);

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(myxml.ToString());
            XmlNodeList xnList = doc.SelectNodes("/abc/Abcd/Segment[@Name='AAA']");
            foreach (XmlNode xn in xnList)
            {
                if (xn.HasChildNodes)
                {
                    foreach (XmlNode item1 in xn.ChildNodes)
                    {
                        Console.WriteLine(item1.InnerText);
                    }
                }
            }

Following is my XML

<Tables>
  <Table name="Test">
    <tablename>TestTable</tablename>
    <refTable>NULL</refTable>
    <refTableIDColumn>NULL</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>OutlineURL</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>OutlineSummary</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>TestForm</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
</Tables>

I am using some codes from google. I want to read nodes having names which is passed dynamically. Extract all the data from it, and send the data as parameter as XML to stored procedure.

Please explain, What node are you trying to extract from shown XML? — codeninja.sj, May 02 '16 at 06:05
Your example code and XML don't match up - what exactly are you trying to get? — Manfred Radlwimmer, May 02 '16 at 06:05
You might have more luck getting an answer if you split your questions (get data from xml, call SP via C#). Second part gets answered here: http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp — Manfred Radlwimmer, May 02 '16 at 06:06
I am using some codes from google. I want to read nodes having names which is passed dynamically. Extract all the data from it, and send the data as parameter as XML to stored procedure. — user2998990, May 02 '16 at 06:07
If it's not your code, don't include it in your question - people will try to "fix" it, even though it's not broken. — Manfred Radlwimmer, May 02 '16 at 06:08
What will you do if the node contains child nodes? Are you going to append all its values?? — codeninja.sj, May 02 '16 at 06:09
Yes. My final XML will be all the nodes as it having node name same as passed dynamically. Then I want to pass the XML file as input to Stored procedure. — user2998990, May 02 '16 at 06:11
In your sample code you are using `...[@Name='AAA']`, but in your XML the attribute "name" has a small "n". XPath is case sensitive... Secondly in your given XML all name attributes have the value "Test". If you want to get answers, you must poste sample code and data which are reduced to the point but must be correct. How should one know what you want? — Shnugo, May 02 '16 at 08:35

score 1 · Accepted Answer · answered May 02 '16 at 09:05

Still I don't know exactly what you want to reach, but this code would get you the content of a given node with all its sub-nodes:

I changed your sample to let the "name" attribute have different values. I'll read the "Test2":

In variable "dummy" you can check the content.

string s = "<Tables>" +
                "<Table name='Test1'>" +
                "<tablename>TestTable</tablename>" +
                "<refTable>NULL</refTable>" +
                "<refTableIDColumn>NULL</refTableIDColumn>" +
                "</Table>" +
                "<Table name='Test2'>" +
                "<tablename>OutlineURL</tablename>" +
                "<refTable>TestTable</refTable>" +
                "<refTableIDColumn>TestTableID</refTableIDColumn>" +
                "</Table>" +
                "<Table name='Test3'>" +
                "<tablename>OutlineSummary</tablename>" +
                "<refTable>TestTable</refTable>" +
                "<refTableIDColumn>TestTableID</refTableIDColumn>" +
                "</Table>" +
                "<Table name='Test4'>" +
                "<tablename>TestForm</tablename>" +
                "<refTable>TestTable</refTable>" +
                "<refTableIDColumn>TestTableID</refTableIDColumn>" +
                "</Table>" +
            "</Tables>";
var ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(s)); ;
System.Xml.Linq.XDocument myxml = System.Xml.Linq.XDocument.Load(ms);

XmlDocument doc = new XmlDocument();
doc.LoadXml(myxml.ToString());
XmlNodeList xnList = doc.SelectNodes("/Tables/Table[@name='Test2']");
foreach (XmlNode xn in xnList) {
    var dummy = xn.OuterXml;
}

score 0 · Answer 2 · answered May 02 '16 at 06:49

You can try following SQL XML query which will parse the XML fragment into a table data which then you can query on it

declare @xml xml = N'
<Tables>
  <Table name="Test">
    <tablename>TestTable</tablename>
    <refTable>NULL</refTable>
    <refTableIDColumn>NULL</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>OutlineURL</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>OutlineSummary</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
  <Table name="Test">
    <tablename>TestForm</tablename>
    <refTable>TestTable</refTable>
    <refTableIDColumn>TestTableID</refTableIDColumn>
  </Table>
</Tables>'

SELECT
 [Table].value('tablename[1]','varchar(100)') AS tablename,
 [Table].value('refTable[1]','varchar(100)') AS refTable,
 [Table].value('refTableIDColumn[1]','varchar(100)') AS refTableIDColumn
FROM @XML.nodes('Tables/Table') TablesList([Table])

I hope it helps,

Read specific node in XML and pass to Stored Procedure in sql server

2 Answers2