SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Data Type as a parameter in a Stored Procedure


XML Data Type as a parameter in a Stored Procedure

Author
Message
miss.delinda
miss.delinda
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 695
Hi,

I've table as follows,

CREATE TABLE [dbo].[majikanAG_subMajikan_1](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[batch_Id] [uniqueidentifier] NOT NULL,
[icNo (Baru)] [varchar](100) NULL,
[icNo (Lama)] [varchar](100) NULL,
[payerNme] [varchar](300) NULL,
[zakatAmount] [decimal](10, 2) NULL,
[subMajikan] [varchar](100) NULL,
CONSTRAINT [PK__majikanA__51EFEBF8002AF460] PRIMARY KEY CLUSTERED
(
[idx] ASC,
[batch_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]




My Stored Procedure as follows,

CREATE PROCEDURE [dbo].[addAGSummary_SubMajikan_Process1]
@agItem xml,
@batch_Id uniqueidentifier output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

set transaction isolation level repeatable read
Begin transaction

Begin Try

select @batch_Id=NEWID()

insert into majikanAG_subMajikan_1(batch_Id, [icNo (Baru)], [icNo (Lama)],
payerNme, zakatAmount, subMajikan)
select @batch_Id,
a.b.value('icNo[1]','varchar(200)') as icNo, --as input1,
a.b.value('icNoOld[1]','varchar(15)') as icNoOld, --as input2,
upper(a.b.value('payerNme[1]','varchar(100)')) as payerNme, --as input3,
--a.b.value('amt[1]','decimal(10,2)') as amt, --as input4,
a.b.value('amt[1]','varchar(100)') as amt, --as input4,
a.b.value('subCd[1]','varchar(100)') as subCd --as input5,
from
@agItem.nodes('/data/ag') a(b)

COMMIT transaction
End Try


Begin Catch
-- Whoops, there was an error
--IF @@TRANCOUNT > 0
ROLLBACK transaction

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

End Catch

END




There are 2 scenario
1- If @agItem did not have so much data (1000 records), the stored procedure run well
2- If @agItem have a so much data (10000 records), the stored procedure cannot process as expected

Why no (2) is happening? Did XML Data Type is not suitable for 10000 records?

Please help. I'm stuck
Vedran Kesegic
Vedran Kesegic
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3798 Visits: 1266
Before executing procedure, what is len(@agItem)? Maybe it is truncated before the procedure.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

miss.delinda
miss.delinda
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 695
Vedran Kesegic (3/20/2013)
Before executing procedure, what is len(@agItem)? Maybe it is truncated before the procedure.


yep. May be it is truncated

My application is ASP.NET. Here the code,

XDocument doc = new XDocument(
new XDeclaration("1.0", "utf-8", "yes"),
new XElement("data"));

//Flag to skip header line of text file
bool isHeaderLine = true;


//string xmlData = "<data>";
string _strFileLocation = MapPath("~/Content/AG-Persekutuan-Bln-09-2011.txt");
string batch_Id = string.Empty;
try
{
//System.Threading.Thread.Sleep(5000);
//myAG objAG = new myAG();

using (StreamReader sr = new StreamReader(_strFileLocation))
{
while (sr.Peek() >= 0)
{
string currentLine = sr.ReadLine();

//Skip first line of file
if (isHeaderLine)
{
isHeaderLine = false;
continue;
}

XElement xmlData = new XElement("ag",
new XElement("icNo", currentLine.Substring(20, 6) + "-" +
currentLine.Substring(26, 2) +
"-" + currentLine.Substring(28, 4)),
new XElement("icNoOld", currentLine.Substring(20, 12)),
new XElement("payerNme", currentLine.Substring(51, 40)),
new XElement("amt", currentLine.Substring(92, 9)),
new XElement("subCd", currentLine.Substring(6, 4))
);

doc.Element("data").Add(xmlData);

}


}


SqlConnection con = new SqlConnection(SQLConn);
con.Open();
SqlCommand cmd = new SqlCommand("addAGSummary_SubMajikan_Process1", con);
cmd.CommandTimeout = 20;
cmd.CommandType = CommandType.StoredProcedure;

//send value to sp
cmd.Parameters.AddWithValue("@agItem", doc.ToString(SaveOptions.DisableFormatting));

SqlParameter newSqlParam = new SqlParameter();
newSqlParam.ParameterName = "@batch_Id";
newSqlParam.SqlDbType = SqlDbType.UniqueIdentifier;
newSqlParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(newSqlParam);

cmd.ExecuteNonQuery();
batch_Id = cmd.Parameters["@batch_Id"].Value.ToString();


//}

//Response.Redirect("_longRunningResult.aspx?batch_Id=" + Server.UrlEncode(batch_Id) + "", false);
lblMsg.Text = "Successful transactions....";
}
catch (Exception ex)
{ lblMsg.Text = ex.Message; }



How to solve the truncate issue on my - cmd.Parameters.AddWithValue("@agItem", doc.ToString(SaveOptions.DisableFormatting));

I'm stuck
Vedran Kesegic
Vedran Kesegic
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3798 Visits: 1266
Try using "new SqlXml(...)" instead of converting doc to string.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Vedran Kesegic
Vedran Kesegic
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3798 Visits: 1266
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ac074ccb-1a52-4bda-a032-c874d3ba8ce5/

cmd.Parameters.Add("@Value", SqlDbType.Xml).Value = doc.ToString();



_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

miss.delinda
miss.delinda
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 695
I think XML Data Type cannot accept so many line. What a terrible
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search