Obtain an XML Document from a SQL Server Query - CSharp Database

CSharp examples for Database:SQL Command

  1. HOME
  2. CSharp
  3. Database
  4. SQL Command

Description

Obtain an XML Document from a SQL Server Query

Demo Code


using System;// w  w w . j a  v  a  2s  .c  o m
using System.Xml;
using System.Data;
using System.Data.SqlClient;

class MainClass
    {
        public static void ConnectedExample()
        {
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = @"Data Source = .\sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                using (SqlCommand com = con.CreateCommand())
                {
                    com.CommandType = CommandType.Text;
                    com.CommandText = "SELECT CustomerID, CompanyName" +
                        " FROM Customers FOR XML AUTO";

                    con.Open();

                    using (XmlReader reader = com.ExecuteXmlReader())
                    {
                        while (reader.Read())
                        {
                            Console.Write("Element: " + reader.Name);
                            if (reader.HasAttributes)
                            {
                                for (int i = 0; i < reader.AttributeCount; i++)
                                {
                                    reader.MoveToAttribute(i);
                                    Console.Write("  {0}: {1}",
                                        reader.Name, reader.Value);
                                }

                                // Move the XmlReader back to the element node.
                                reader.MoveToElement();
                                Console.WriteLine(Environment.NewLine);
                            }
                        }
                    }
                }
            }
        }

        public static void DisconnectedExample()
        {
            XmlDocument doc = new XmlDocument();

            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = @"Data Source = .\sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText =
                    "SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO";

                con.Open();

                XmlReader reader = com.ExecuteXmlReader();
                doc.LoadXml("<results></results>");

                XmlNode newNode = doc.ReadNode(reader);

                while (newNode != null)
                {
                    doc.DocumentElement.AppendChild(newNode);
                    newNode = doc.ReadNode(reader);
                }
            }

            Console.WriteLine(doc.OuterXml);
        }

        public static void Main(string[] args)
        {
            ConnectedExample();

            DisconnectedExample();

        }
    }

Result


Related Tutorials