none
how can I get the SQLS inside the message sign of the XML. Please help RRS feed

  • Question

  • Here I have given  the XML file below. I want to store  the sql into the list variable

    <?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>

    My code is as given below

    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));
                        }
                    }
                }
    
    
    
    public static 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); // Here I want to add each sql statement inside the <!--  --!>
    
                            continue;
                        }
                        if (item.HasChildNodes)
                        {
                            result.AddRange(GetSQL(item));
                        }
                    }
                }
    


    polachan

    Thursday, July 4, 2019 8:54 AM

All replies

  • Hi polachan,

    Thank you for posting here.

    Based on your description, you want to add three sql sentences to a list from a xml file.

    You could try the following code.

     XmlReaderSettings settings = new XmlReaderSettings();
                settings.DtdProcessing = DtdProcessing.Parse;
                XmlReader reader = XmlReader.Create("D:\\test.xml", settings);
                reader.MoveToContent();
                List<string> list = new List<string>();
                while (reader.Read())
                {
                    switch (reader.NodeType)
                    {
                        case XmlNodeType.Comment:
                            list.Add(reader.Value.Trim());
                            break;
                    }
                }

    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 9:25 AM
    Moderator
  • Jack

    can you check my xml structure and my code. I want to read the Each SQL inside the Customer tag  and  have to store List<String> variable in a loop . In Customer tag there would be more than one sql tag .

    int Counter = 0

    List<ccounter> = SQL  1 of the customer Tag

    int Counter = 1

    List<ccounter> = SQL 2  of the same customer Tag 

    In Next Customer tag the counter would be initialised to 0.

    Please can you help my code. 

    Christoph helped me  in my previous thread but only the problem the 'inner text is not working'

    Pol



    polachan

    Thursday, July 4, 2019 9:47 AM
  • if you replace

    result.Add(item.InnerText);

    with

    result.Add(item.FirstChild.InnerText);

    you get the SQL without the comment 'brackets', but still with leading/trailing "/r/n" if you want these removed, you can add some RegEx to the line:

    result.Add(new Regex("[^ -~]+").Replace(item.FirstChild.InnerText, ""));

    Thursday, July 4, 2019 12:12 PM
  • Hi polachan,

    Thanks for the feedback.

    According to your advice, I have updated my code.

    Code:

     string path = "D:\\test.xml";
                XmlReaderSettings settings = new XmlReaderSettings();
                settings.DtdProcessing = DtdProcessing.Parse;
                List<string> list = new List<string>();
                using (XmlReader reader = XmlReader.Create(path,settings))
                {
                    while (reader.ReadToFollowing("CUSTOMER"))
                    {
                       while(reader.ReadToFollowing("SQL"))
                        {
                            while(reader.Read())
                            {
                                switch (reader.NodeType)
                                {
                                       case XmlNodeType.Comment:
                                        list.Add(reader.Value.Trim());
                                        break;
                                }
                            }
                        }
                     
                    }
                }

    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.

    Friday, July 5, 2019 1:52 AM
    Moderator