Add attribute to XML in ASP.NET webservices

Question

I'm new to C# and ASP.NET

I managed to put MS SQL Server database into xml document, but what I'm trying to do now is, to add an attribute to the XML, which will be an id from the database.

something like:

<Products>
    <product id=1>
        <name>Some name</name> 
    </product>
    <product id=2>
        <name>Some other</name> 
    </product>
    <product id=3>
        <name>Some other name</name> 
    </product>
</Products>

This is what I have so far:
products.asmx.cs:

[WebMethod]
public XmlDocument productsAll()
{
    string conString = "Data Source=my_db.com;Integrated Security=True";
    SqlConnection sqlConn = new SqlConnection(conString);

    SqlCommand sqlCmd = sqlConn.CreateCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "SELECT * FROM Products";

    SqlDataAdapter dataAdptr = new SqlDataAdapter();
    dataAdptr.SelectCommand = sqlCmd;
    DataSet dsProducts = new DataSet("Products");
    dataAdptr.Fill(dsProducts, "product");

    XmlDocument xmlDom = new XmlDocument();
    xmlDom.LoadXml(dsProducts.GetXml());
    return xmlDom;
}

.asmx web services are dead - check out either WCF or the new ASP.NET WebAPI for REST services — marc_s
– marc_s, Commented Feb 22, 2015 at 11:27

marc_s · Accepted Answer · 2015-02-22 11:41:38Z

2

The easiest way would be to create the XML in SQL Server and just read out the resulting XML.

Try something like this:

public XmlDocument productsAll()
{
    // define connection string
    string conString = "Data Source=my_db.com;Integrated Security=True";

    // define SQL query to use 
    string query = "SELECT ID AS '@ID', Name FROM dbo.Products FOR XML PATH('Product'), ROOT('Products')";

    // set up connection and command objects
    using (SqlConnection sqlConn = new SqlConnection(conString))
    using (SqlCommand sqlCmd = new SqlCommand(query, sqlConn))
    {
       // open connection
       sqlConn.Open();

       // execute query, read out the XML from the T-SQL query
       string xmlContents = sqlCmd.ExecuteScalar().ToString();

       // close connection
       sqlConn.Close();

       // parse XML received from SQL Server into a XmlDocument and return
       XmlDocument xmlDom = new XmlDocument();
       xmlDom.LoadXml(xmlContents);

       return xmlDom;
    }
}

answered Feb 22, 2015 at 11:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

knowbody Over a year ago

I get the error SQLException was unhandled... on string xmlContents

marc_s Over a year ago

@knowbody: your connection string is incomplete - you have a Data Source (a server name), but no database name..... try something like: string conString = "server=my_db.com;database=XXXXXXXXX;Integrated Security=True; and replace the XXXXXX with whatever your actual database name is

knowbody Over a year ago

it's all good now. ^ that, plus you could do sqlCmd.ExecuteScalar as string in case you get null but all works fine. super old school. thank you

Raj · Accepted Answer · 2015-02-22 11:27:23Z

0

I'm answering from a linux machine so don't have SQL Server handy right now. Please check T-SQL help for the SELECT FOR XML. You can control the structure of the resulting XML.

answered Feb 22, 2015 at 11:27

Raj

1,7701 gold badge12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Add attribute to XML in ASP.NET webservices

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related