[C#] DB 에서 XML 형식으로 가져오기


        [WebMethod(Description = "CountryCodeToXML 2023.11.01")]
        public XmlElement CountryCodeToXML()
        {
            //SQL 연결설정
            SqlConnection sqlConnection;
            SqlDataAdapter sqlDataAdapter;

            string connectionString = "Server=111.111.111.111;User ID=MYID;Password=MYPASSWORD123;Database=MYDBName";

            sqlConnection = new SqlConnection(connectionString);

            XmlDocument xml = new XmlDocument();

            try
            {
                DataSet COUNTRY_CODE;

                //exec COUNTRY_CODE
                sqlDataAdapter = new SqlDataAdapter("COUNTRY_CODE", sqlConnection);
                sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

                //SqlParameter paramAGENT = new SqlParameter("@AGENT", SqlDbType.VarChar, 20);
                //paramAGENT.Value = AGENT;
                //sqlDataAdapter.SelectCommand.Parameters.Add(paramAGENT);

                //SqlParameter paramLanguage = new SqlParameter("@Language", SqlDbType.VarChar, 2);
                //paramLanguage.Value = Language;
                //sqlDataAdapter.SelectCommand.Parameters.Add(paramLanguage);
                ////

                COUNTRY_CODE = new DataSet();
                sqlDataAdapter.Fill(COUNTRY_CODE, "COUNTRY_CODE");

                xml.PreserveWhitespace = true;
                xml.LoadXml(COUNTRY_CODE.GetXml());
                

            }
            catch //(Exception ex)
            {
                //클라이언트에게 예외를 던진다.
                //throw ex;
            }

            finally
            {
                sqlConnection.Close();
            }

            return xml.DocumentElement;

        }

[WebMethod(Description = "CountryCodeToXML 2023.11.01")]

public XmlElement CountryCodeToXML()

{

//SQL 연결설정

SqlConnection sqlConnection;

SqlDataAdapter sqlDataAdapter;

string connectionString = "Server=111.111.111.111;User ID=MYID;Password=MYPASSWORD123;Database=MYDBName";

sqlConnection = new SqlConnection(connectionString);

XmlDocument xml = new XmlDocument();

try

{

DataSet COUNTRY_CODE;

//exec COUNTRY_CODE

sqlDataAdapter = new SqlDataAdapter("COUNTRY_CODE", sqlConnection);

sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

//SqlParameter paramAGENT = new SqlParameter("@AGENT", SqlDbType.VarChar, 20);

//paramAGENT.Value = AGENT;

//sqlDataAdapter.SelectCommand.Parameters.Add(paramAGENT);

//SqlParameter paramLanguage = new SqlParameter("@Language", SqlDbType.VarChar, 2);

//paramLanguage.Value = Language;

//sqlDataAdapter.SelectCommand.Parameters.Add(paramLanguage);

////

COUNTRY_CODE = new DataSet();

sqlDataAdapter.Fill(COUNTRY_CODE, "COUNTRY_CODE");

xml.PreserveWhitespace = true;

xml.LoadXml(COUNTRY_CODE.GetXml());

}

catch //(Exception ex)

{

//클라이언트에게 예외를 던진다.

//throw ex;

}

finally

{

sqlConnection.Close();

}

return xml.DocumentElement;

}

결과


<NewDataSet>
<COUNTRY_CODE>
<CountryCode>AD</CountryCode>
<AreaCode>E1</AreaCode>
<EName>Andorra</EName>
<KName>안도라 공국</KName>
<OrderNo>0</OrderNo>
<UseFlag>Y</UseFlag>
<StayInd xml:space="preserve"> </StayInd>
<SubAreaCode>E1</SubAreaCode>
<CountryNumber>+376</CountryNumber>
</COUNTRY_CODE>
<COUNTRY_CODE>
<CountryCode>AE</CountryCode>
<AreaCode>M1</AreaCode>
<EName>United Arab Emirates</EName>
<KName>아랍에미리트</KName>
<OrderNo>0</OrderNo>
<UseFlag>Y</UseFlag>
<AreaCodeNew>M1</AreaCodeNew>
<StayInd xml:space="preserve"> </StayInd>
<SubAreaCode>M1</SubAreaCode>
<CountryNumber>+971</CountryNumber>
</COUNTRY_CODE>
<COUNTRY_CODE>
<CountryCode>AF</CountryCode>
<AreaCode>D1</AreaCode>
<EName>Afghanistan</EName>
<KName>아프가니스탄</KName>
<OrderNo>0</OrderNo>
<UseFlag>Y</UseFlag>
<StayInd xml:space="preserve"> </StayInd>
<SubAreaCode>D1</SubAreaCode>
<CountryNumber>+93</CountryNumber>
</COUNTRY_CODE>
:: 중략 ::

<COUNTRY_CODE>

<EName>Andorra</EName>

</COUNTRY_CODE>

<COUNTRY_CODE>

<EName>United Arab Emirates</EName>

<KName>아랍에미리트</KName>

</COUNTRY_CODE>

<COUNTRY_CODE>

<EName>Afghanistan</EName>

<KName>아프가니스탄</KName>

</COUNTRY_CODE>

:: 중략 ::