Export my SQL Database into an XML using WPF C#

Question

I want to use my WPF C# App code to export a database and save it on an XML file. Here's the code I'm using.

private void btnSave_Click(object sender, RoutedEventArgs e)
{
    string FILENAME = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Connection.xml");

    XDocument xdoc = XDocument.Load(FILENAME);

    string conn = xdoc.Descendants("connectionStrings").FirstOrDefault().Value;

    string query = "SELECT TOP 1000 [EMPID], [EMPName], [EMPRole], [EMPAddress], [EMPEmail], [EMPNumber] FROM [Login].[dbo].[tblEMP1] FOR XML PATH('ry'), ROOT('ty') ";

    using (SqlConnection sqlCon = new SqlConnection(conn))
    using (SqlCommand sqlCmd = new SqlCommand(query, sqlCon))
    { 
        sqlCon.Open();
        string result = sqlCmd.ExecuteScalar().ToString();
        MessageBox.Show("XML Saved");
        sqlCon.Close();

        File.WriteAllText(@"C:\Users\user12\Desktop\test3.xml", result);
    }
}

The problem is it does not display the xml tags as I would like for example <ty>,<ry>,<EMPID>,<EMPName> etc. do not appear. The XML runs fine on Microsoft SQL Server Management Studio. This is what the XML looks like when I run it on Microsoft SQL Server Management Studio.

<ty>
  <ry>
    <EMPID>1</EMPID>
    <EMPName>Thabo</EMPName>
    <EMPRole>Developer </EMPRole>
    <EMPAddress>227 Complex B WoodMans Road  Claremont</EMPAddress>
    <EMPEmail>[email protected]</EMPEmail>
    <EMPNumber>083 577 8910</EMPNumber>
  </ry>
  <ry>
    <EMPID>2</EMPID>
    <EMPName>Aldrin</EMPName>
    <EMPRole>Analyst   </EMPRole>
    <EMPAddress>65 Mfecane Avenue</EMPAddress>
    <EMPEmail>[email protected]</EMPEmail>
    <EMPNumber>0872343352</EMPNumber>
  </ry>
  <ry>
    <EMPID>4</EMPID>
    <EMPName>Amoleng</EMPName>
    <EMPRole>Engineer  </EMPRole>
    <EMPAddress>43 Pixely KaSeme Street</EMPAddress>
    <EMPEmail>[email protected]</EMPEmail>
    <EMPNumber>0765546832</EMPNumber>
  </ry>

</ty>

But when I save it on using my app on visual studio. This is what I get as a result(when opened with a browser as an XML):

1ThaboDeveloper 227 Complex B WoodMans Road [email protected] 577 89102AldrinAnalyst 65 Mfecane [email protected] 43 Pixely KaSeme [email protected]

As you can see everything is clustered with no tags

This is what happens When I open it with notepad;

<ty><ry><EMPID>1</EMPID><EMPName>Thabo</EMPName><EMPRole>Developer </EMPRole><EMPAddress>227 Complex B WoodMans Road  Claremont</EMPAddress><EMPEmail>[email protected]</EMPEmail><EMPNumber>083 577 8910</EMPNumber></ry><ry><EMPID>2</EMPID><EMPName>Aldrin</EMPName><EMPRole>Analyst   </EMPRole><EMPAddress>65 Mfecane Avenue</EMPAddress><EMPEmail>[email protected]</EMPEmail><EMPNumber>0872343352</EMPNumber></ry><ry><EMPID>4</EMPID><EMPName>Amoleng</EMPName><EMPRole>Engineer  </EMPRole><EMPAddress>43 Pixely KaSeme Street</EMPAddress><EMPEmail>[email protected]</EMPEmail><EMPNumber>0765546832</EMPNumber></ry>

As you can see the tags appear now but its still clustered.

My goal is to make a ready to use XML that won't require additional manipulating and editing and I want the tags to appear all the time with proper layout as it does in SQL Server Management Studio example.

Thanks in advance.

Have you tried reading/processing the Xml file in another program and does it give errors? From what I can see the Notepad output is fine, the formatting is dependent on the Editor you use and doesn't matter to most parsers. — JayV
– JayV, Commented Jun 19, 2021 at 21:00
ExecuteScalar is the wrong method for that query. See learn.microsoft.com/en-us/dotnet/api/… — David Browne - Microsoft
– David Browne - Microsoft, Commented Jun 20, 2021 at 0:12
The issue may be with notepad. Microsoft recently updated notepad to support linux. Since this update I get same results you are getting. I tried the patch but it didn't work. See : devblogs.microsoft.com/commandline/extended-eol-in-notepad — jdweng
– jdweng, Commented Jun 20, 2021 at 7:25

Yitzhak Khabinsky · Accepted Answer · 2021-06-20 16:32:48Z

Please try the following solution.

c#

void Main()
{
    const string OUTFILENAME = @"C:\Users\user12\Desktop\test3.xml";

    try
    {
        string FILENAME = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Connection.xml");
        XDocument xdoc = XDocument.Load(FILENAME);
        string conn = xdoc.Descendants("connectionStrings").FirstOrDefault().Value;

        using (SqlConnection con = new SqlConnection(conn))
        using (SqlCommand cmd = new SqlCommand())
        {
            // dynamic SQL
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT TOP 1000 [EMPID], [EMPName], [EMPRole], [EMPAddress], [EMPEmail], [EMPNumber] FROM [Login].[dbo].[tblEMP1] FOR XML PATH('ry'), TYPE, ROOT('ty')";

            con.Open();

            using (XmlReader reader = cmd.ExecuteXmlReader())
            {
                XDocument xdocoutput = XDocument.Load(reader);

                var settings = new XmlWriterSettings();
                settings.Indent = true;
                settings.OmitXmlDeclaration = false;
                settings.IndentChars = "\t";
                // to remove BOM
                settings.Encoding = new UTF8Encoding(false);

                using (var writer = XmlWriter.Create(OUTFILENAME, settings))
                {
                    xdocoutput.Save(writer);
                }
            }
            Console.WriteLine("File '{0}' has been created.", OUTFILENAME);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Connection failed with the following exception...");
        Console.WriteLine(ex.Message.ToString());
    }
}

JustAnotherDev · Accepted Answer · 2021-06-20 00:25:12Z

1

If you look at your raw xml (when opened with notpad) you are missing a closing </ty> tag

<ty>
<ry>[snipped content]</ry>
<ry>[snipped content]</ry>

There is no closing </ty> tag, which is why the browser is rendering it as a single line, not all browsers do this some (chrome) give you a hint of the error:

Check if you're getting this closing tag back from sqlCmd.ExecuteScalar().ToString(); or not. It's getting truncated/removed somewhere along the line (assuming your notepad copy/paste is accurate)

answered Jun 20, 2021 at 0:25

JustAnotherDev

6823 silver badges17 bronze badges

1 Comment

Mholi Zindela Over a year ago

It seems my method was cutting some of the xml

Collectives™ on Stack Overflow

Export my SQL Database into an XML using WPF C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related