none
how can get the sql statement in a list<string> variable from XML file RRS feed

  • Question

  • Hi

    I am trying to store the  all the sqls  inside the tag of the xml file.

    XmlDocument doc = new XmlDocument();
                string docxml = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) + "\\" + sqlxml;
                doc.Load(xml);
    
               
    
                XmlNodeList subjectList = doc.GetElementsByTagName("Customer");
                for (int cnt = 0; cnt < subjectList.Count; cnt++)
                {
                     List<string> sql_strings = Here I want to store sqlstatement in to the variable sql_strings1 
    
                }
     

    when  it read the element first customer tag  there two sqls .So the result should be 

    Sql_Strings[1] = SELECT * FROM CUSTOMER WHERE CODE = '123'

    Sql_Strings[2] = SELECT * FROM CUSTOMER WHERE CODE = '567'

    When the  counter cnt =2 , the  Sql_string should be 

    Sql_Strings[1] = SELECT * FROM CUSTOMER WHERE CODE = '1234'

    XML file

    <?xml version="1.0" encoding="utf-8" ?>
    <STATEMENT>
    <CUSTOMER ID = "CUSTOMER1">
    <SQL ID="SQLS1">
     SELECT * FROM CUSTOMER WHERE CODE = '123'
     </SQL>
    <SQL ID="SQLS2">
     SELECT * FROM CUSTOMER WHERE CODE = '567'
     </SQL>
    <Address>test@gmail.com</Address>
    <Address>test1@gmail.com</Address>
    <Address></Address>
    <MailSubject>Please find statement</MailSubject>
    <MailBody>Please find statement</MailBody>
    
    <FILENAME>file1</FILENAME>
    
    </CUSTOMER>
    <CUSTOMER ID = "CUSTOMER2">
    <SQL ID="SQLS">
       
     SELECT * FROM CUSTOMER WHERE CODE = '1234' 
    
     </SQL>
    <Address>test@gmail.com</Address>
    <Address>test1@gmail.com</Address>
    <Address></Address>
    <MailSubject>Please find statement</MailSubject>
    <MailBody>Please find statement</MailBody>
    <FILENAME>file2</FILENAME>
    
    </CUSTOMER>
    </STATEMENT>
    Please help to get the sql like that way in to the string variable. 


    polachan

    Wednesday, July 3, 2019 9:02 AM

Answers

  • You have to iterate through each customer in your subjectList (please note that this is case sensitive):

    XmlNodeList subjectList = doc.GetElementsByTagName("CUSTOMER");
    
                var result = new List<String>();
    
                foreach (var child in subjectList)
                {
                    if (child is XmlNode)
                    {
                        var element = (XmlNode) child;
                        if (element.HasChildNodes)
                        {
                            result.AddRange(GetSQL(element));
                        }
                    }
                }

    and the GetSQL(...) will get the SQL-Node's values. I did this recursively just to be safe if XML structure changes:

            private IEnumerable<string> GetSQL(XmlNode element)
            {
                var result = new List<String>();
    
                foreach (var child in element.ChildNodes)
                {
                    if (child is XmlNode)
                    {
                        var item = (XmlNode) child;
                        if (item.Name == "SQL")
                        {
                            result.Add(item.InnerText);
                            continue;
                        }
                        if (item.HasChildNodes)
                        {
                            result.AddRange(GetSQL(item));
                        }
                    }
                }
    
                return result;
            }

    • Marked as answer by polachan Thursday, July 4, 2019 10:57 AM
    Wednesday, July 3, 2019 10:40 AM

All replies

  • You have to iterate through each customer in your subjectList (please note that this is case sensitive):

    XmlNodeList subjectList = doc.GetElementsByTagName("CUSTOMER");
    
                var result = new List<String>();
    
                foreach (var child in subjectList)
                {
                    if (child is XmlNode)
                    {
                        var element = (XmlNode) child;
                        if (element.HasChildNodes)
                        {
                            result.AddRange(GetSQL(element));
                        }
                    }
                }

    and the GetSQL(...) will get the SQL-Node's values. I did this recursively just to be safe if XML structure changes:

            private IEnumerable<string> GetSQL(XmlNode element)
            {
                var result = new List<String>();
    
                foreach (var child in element.ChildNodes)
                {
                    if (child is XmlNode)
                    {
                        var item = (XmlNode) child;
                        if (item.Name == "SQL")
                        {
                            result.Add(item.InnerText);
                            continue;
                        }
                        if (item.HasChildNodes)
                        {
                            result.AddRange(GetSQL(item));
                        }
                    }
                }
    
                return result;
            }

    • Marked as answer by polachan Thursday, July 4, 2019 10:57 AM
    Wednesday, July 3, 2019 10:40 AM
  • Hi polachan,

    Thank you for posting here.

    For your question, you want to add three sql sentences to a list from a xml file.

    I suggest that you could use XDocument to solve the problem. I make a simple code, you could have a look.

    Code:

     static void Main(string[] args)
            {
                XDocument doc = XDocument.Load("D:\\test.xml");
                var m = doc.Descendants("SQL");
                List<string> list = new List<string>();
                foreach (var item in m)
                {
                    list.Add(item.Value.Trim());
                }
                Console.ReadKey();
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 4, 2019 5:47 AM
    Moderator
  • Christoph

    Firstly I apoligise for the changes in XML . To Apply long sql query I have changed the  the SQL element. I have given the message sign (<!-- before the query. 

    So the code 

    if (item.Name == "SQL") { result.Add(item.InnerText); continue; } is not working here the SQL statement is not storing here

    I have to store the the sql into list variable inside the comment . Please can you give your updated code

    MY XML with message element  is as follows

    <?xml version="1.0" encoding="utf-8" ?>
    <STATEMENT>
    <CUSTOMER ID = "CUSTOMER1">
    <SQL ID="SQLS1">
    <!--
     SELECT * FROM statement WHERE CODE = '123' and Invdate >= GetDate()
    -->
     </SQL>
    <SQL ID="SQLS2">
     <!--
     SELECT * FROM statement WHERE CODE = '1234' and Invdate >= GetDate()
    -->
     </SQL>
    <Address>test@gmail.com</Address>
    <Address>test1@gmail.com</Address>
    <Address></Address>
    <MailSubject>Please find statement</MailSubject>
    <MailBody>Please find statement</MailBody>
    
    <FILENAME>file1</FILENAME>
    
    </CUSTOMER>
    <CUSTOMER ID = "CUSTOMER2">
    <SQL ID="SQLS">
    <!--
     SELECT * FROM statement WHERE CODE = '12345' and Invdate >= GetDate()
    -->
    
     </SQL>
    <Address>test@gmail.com</Address>
    <Address>test1@gmail.com</Address>
    <Address></Address>
    <MailSubject>Please find statement</MailSubject>
    <MailBody>Please find statement</MailBody>
    <FILENAME>file2</FILENAME>
    
    </CUSTOMER>
    </STATEMENT>


    polachan

    Thursday, July 4, 2019 8:47 AM
  • Hi polachan,

    Thanks for the feedback.

    Since you changed the thread, which causes the answer will change. Therefore, I think you could mark the valid response to the original question as an answer. For your new question, our team will reply it in an new thread because one thread for one question.

    Thanks for understanding.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 4, 2019 9:41 AM
    Moderator
  • Done. Many Thanks

    polachan

    Thursday, July 4, 2019 10:58 AM