locked
Linq to XML Issue RRS feed

  • Question

  • User-1188570427 posted

    I am passing an XML document to my stored procedure. Is there a way I can pass in the declaration as well? Right now I do a Stringbuilder and append the declaration first and then do a xmldoc.ToString() to pass in the whole thing. Is there a way to do just xmldoc.ToString() where it will know to include the declaration?

    Thursday, May 2, 2013 8:21 PM

Answers

  • User260886948 posted

    Hi,

    Please try to refer to the following code:

    The Sample.xml:

    <?xml version="1.0" encoding="utf-8" ?>
    <users>
    <user>
    <FirstName>Suresh</FirstName>
    <LastName>Dasari</LastName>
    <UserName>SureshDasari</UserName>
    <Job>Team Leader</Job>
    </user>
    <user>
    <FirstName>Mahesh</FirstName>
    <LastName>Dasari</LastName>
    <UserName>MaheshDasari</UserName>
    <Job>Software Developer</Job>
    </user>
    <user>
    <FirstName>Madhav</FirstName>
    <LastName>Yemineni</LastName>
    <UserName>MadhavYemineni</UserName>
    <Job>Business Analyst</Job>
    </user>
    </users>

    Then send this xml file as parameter to stored procedure for that first create xml file in your application (Right Click your application and select Add New Item >> Select XML file) and give name as “Sample.xml” and write following code in your aspx page like this:

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <title>Send xml file as a parameter to SQL stored procedure in asp.net</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:Button ID="btnSend" Text="Send XML to Database" runat="server" onclick="btnSend_Click" /><br /><br />
    <b>Inserted Records Details</b> :
    <asp:GridView ID="gvDetails" runat="server">
    <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
    </asp:GridView>
    </div>
    </form>
    </body>
    </html>

    In the .cs:

    protected void btnSend_Click(object sender, EventArgs e)
    {
    XmlTextReader xmlreader = new XmlTextReader(Server.MapPath("Sample.xml"));
    DataSet ds = new DataSet();
    ds.ReadXml(xmlreader);
    xmlreader.Close();
    if (ds.Tables.Count != 0)
    {
    using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
    {
    con.Open();
    SqlCommand cmd = new SqlCommand("prc_readxmldata", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml();
    SqlDataAdapter da=new SqlDataAdapter(cmd);
    DataSet ds1=new DataSet();
    da.Fill(ds1);
    gvDetails.DataSource = ds1;
    gvDetails.DataBind();
    con.Close();
    }
    }
    }

    Then for the stored procedure “prc_readxmldata”, we need to create that stored procedure in database like as shown below:

    CREATE PROCEDURE prc_readxmldata
    (
    @XMLdata XML
    )
    AS
    BEGIN
    SELECT
    t.value('(FirstName/text())[1]','nvarchar(120)')AS FirstName ,
    t.value('(LastName/text())[1]','nvarchar(120)')AS LastName,
    t.value('(UserName/text())[1]','nvarchar(120)')AS UserName,
    t.value('(Job/text())[1]','nvarchar(120)')AS Job
    FROM
    @XMLdata.nodes('/users/user')AS TempTable(t)
    END
    

    Hope it can help you.

    Best Regards,
    Amy Peng
     


     




    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 10, 2013 5:00 AM