Create XML from SQL select query

I am trying to create a xml from a SQL select, but I can not insert “:” (like cac:PartyTaxScheme), neither can put 2 data in one element, look element “cbc:CompanyID” (<cbc:CompanyID schemeName=”31″ schemeID=”0″ schemeAgencyID=”195″>900711000</cbc:CompanyID> ), how can I do it ? Next is the example I want to expect:

<cac:PartyTaxScheme>
<cbc:RegistrationName>GRUPO FAM</cbc:RegistrationName>
        <cbc:CompanyID schemeName="31" schemeID="0" schemeAgencyID="195">900711000</cbc:CompanyID>
        <cbc:TaxLevelCode listName="48">O-23</cbc:TaxLevelCode>
        <cac:RegistrationAddress>
            <cbc:ID>11001</cbc:ID>
            <cbc:CountrySubentityCode>11</cbc:CountrySubentityCode>
            <cac:AddressLine>
                <cbc:Line>CaLL 90</cbc:Line>
            </cac:AddressLine>
            <cac:Country>
                <cbc:IdentificationCode>CO</cbc:IdentificationCode>
            </cac:Country>
        </cac:RegistrationAddress>
</cac:PartyTaxScheme>

Thanks a lot

Answer

Using Xml Linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string ident = "<?xml version="1.0" encoding="utf-8" ?>" +
                "<cac:PartyTaxScheme xmlns:cac="URL1" xmlns:cbc="URL2">" +
                "</cac:PartyTaxScheme>";

            XDocument doc = XDocument.Parse(ident);

            string registrationName = "GPUPO FAM";
            string schemeName = "31";
            string schemeID = "0";
            string schemeAgencyID = "195";
            string companyID = "900711000";
            string listName = "48";
            string taxLevelCode = "0-23";
            string ID = "11001";
            string countrySubentityCode = "11";
            string address = "CaLL 90";
            string identificationCode = "CO";

            XElement partyTaxScheme = doc.Root;
            XNamespace nsCac = partyTaxScheme.GetNamespaceOfPrefix("cac");
            XNamespace nsCbc = partyTaxScheme.GetNamespaceOfPrefix("cbc");
            XElement xRegistrationName = new XElement(nsCac + "RegistrationName", registrationName);
            partyTaxScheme.Add(xRegistrationName);

            XElement xCompanyID = new XElement(nsCbc + "CompanyID", new object[] {
                new XAttribute("scnemeName", schemeName),
                new XAttribute("schemeID", schemeID),
                new XAttribute("schemeAgencyID", schemeAgencyID),
                companyID
            });
            partyTaxScheme.Add(xCompanyID);

            XElement xTaxLevelCode = new XElement(nsCbc + "TaxLevelCode", new object[] {
                new XAttribute("listName", listName),
                taxLevelCode
            });
            partyTaxScheme.Add(xTaxLevelCode);

            XElement xRegistrationAddress = new XElement(nsCac + "RegistrationAddress");
            partyTaxScheme.Add(xRegistrationAddress);

            xRegistrationAddress.Add(new XElement(nsCbc + "ID", ID));
            xRegistrationAddress.Add(new XElement(nsCbc + "CountrySubentityCode", countrySubentityCode));
            xRegistrationAddress.Add(new XElement(nsCac + "AddressLine", new XElement(nsCbc + "Line", address)));
            xRegistrationAddress.Add(new XElement(nsCac + "Country", new XElement(nsCbc + "IdentificationCode", countrySubentityCode)));
 
 
        }
    }
}