passing xml document to stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

passing xml document to stored procedure

Author

Topic

moodi_z
Starting Member

38 Posts

Posted - 2009-06-29 : 09:42:12

Hi,

I'm getting this error message while attempting to pass xml document to stored procedure:
"No mapping exists from object type System.Xml.XmlDocument to a known managed provider native type."

I need to pass xml doc and not it's content as a string because I'm using : @XMLdoc.nodes

What is that error message about?

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 09:50:15

Pass ONLY @XMLdoc variable.

N 56°04'39.26"
E 12°55'05.63"

moodi_z
Starting Member

38 Posts

Posted - 2009-06-29 : 10:09:08

When I changed it to:

XmlDocument doc = new XmlDocument();
doc.LoadXml(txtWriter.ToString());
SqlParameter param = new SqlParameter("@XMLdoc", SqlDbType.Xml);
param.Value = doc.InnerXml;
cmd.Parameters.Add(param);

I got:
SELECT INTO failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 10:11:38

The code above has NOTHING with SQL Server to do.
Pleas post the SQL Server code, which accidentally also is the code providing the error.

N 56°04'39.26"
E 12°55'05.63"

moodi_z
Starting Member

38 Posts

Posted - 2009-06-29 : 10:19:09

this is my sp:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Added by Muhammad
SET ARITHABORT ON
GO

-- =============================================
-- Author: <Author,,Z. Muhammad>
-- Create date: <Create Date,,20/05/2009>
-- Description: <Description,,>
-- =============================================

ALTER PROCEDURE [dbo].[sp_InsertPODetails]
@XMLdoc AS xml,
@Err_Description AS varchar(100) output

AS
BEGIN

SET NOCOUNT ON;
SET DATEFORMAT dmy;

DECLARE @POID varchar(100),
@RouteID varchar(100),
@CatalogID varchar(100),
@RequestedQty varchar(25),
@LotID varchar(100),
@Priority varchar(25),
@UserID varchar(100)

SET @Err_Description = ''

/*****************************************************/
/** Read PO information from xml and validate data **/
/*****************************************************/

-- PO Data
SELECT p.n.value('POID[1]', 'VARCHAR(100)') AS POID,
p.n.value('RouteID[1]', 'VARCHAR(100)') AS RouteID,
p.n.value('CatalogID[1]', 'VARCHAR(100)') AS CatalogID,
p.n.value('RequestedQty[1]', 'VARCHAR(25)') AS RequestedQty,
p.n.value('LotID[1]', 'VARCHAR(100)') AS LotID,
p.n.value('Priority[1]', 'VARCHAR(25)') AS Priority,
p.n.value('UserID[1]', 'VARCHAR(100)') AS UserID
INTO #TMP1
FROM @XMLdoc.nodes('POInfo/PO') AS p(n)

-- Validate Data
DECLARE PO_Cursor SCROLL CURSOR FOR
SELECT POID, RouteID, CatalogID, RequestedQty, LotID, Priority, UserID
FROM #TMP1

OPEN PO_Cursor

FETCH FIRST FROM PO_Cursor
INTO @POID, @RouteID, @CatalogID, @RequestedQty, @LotID, @Priority, @UserID

/*************************************************************************/
/** Get PO Tags information by associations and insert to temp table **/
/*************************************************************************/

SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value],
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
INTO #TMP3
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID
WHERE (tbl_TagLinks.AssociationType = 100)

UNION

SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value],
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID INNER JOIN
tbl_Routes ON tbl_TagLinks.UID1 = tbl_Routes.RouteUID
WHERE (tbl_TagLinks.AssociationType = 101) AND
(tbl_Routes.RouteID=@RouteID)

DECLARE Tags_Cursor SCROLL CURSOR FOR
SELECT TaglinkUID, TagDefId, [Value], ControlType, MinValue, MaxValue, [SQL]
FROM #TMP3
ORDER BY TagDefId

OPEN Tags_Cursor

BEGIN TRANSACTION POTransaction

WHILE @@FETCH_STATUS = 0
BEGIN

-----------------------------------------------------
DECLARE @Catalog_Value varchar(255)

SELECT @Catalog_Value =Value
FROM tbl_d_Catalog
WHERE (CatalogID =@CatalogID) AND
(TagDefId = 'NotApproved_to_production')
-----------------------------------------------------

IF @POID = ''
SET @Err_Description = 'POID is empty.'

ELSE IF EXISTS(Select POID From tbl_PO Where POID = @POID )
SET @Err_Description = 'POID ' + @POID + ' already exists.'

ELSE IF @RouteID = ''
SET @Err_Description = 'RouteID is empty.'

ELSE IF NOT EXISTS(Select RouteID From tbl_Routes Where RouteID = @RouteID )
SET @Err_Description = 'RouteID ' + @RouteID + ' does not exist.'

ELSE IF @CatalogID = ''
SET @Err_Description = 'CatalogID is empty.'

ELSE IF NOT EXISTS(Select CatalogID From tbl_Catalog Where CatalogID = @CatalogID )
SET @Err_Description = 'CatalogID ' + @CatalogID + ' does not exist.'

ELSE IF NOT EXISTS(Select CatalogID From tbl_Catalog INNER JOIN
tbl_RouteItems ON tbl_Catalog.CatalogUID = tbl_RouteItems.CatalogUID INNER JOIN
tbl_Routes ON tbl_RouteItems.RouteUID = tbl_Routes.RouteUID
Where tbl_Routes.RouteID=@RouteID AND CatalogID=@CatalogID )
SET @Err_Description = 'CatalogID ' + @CatalogID + ' does not belong to route ' + @RouteID + '.'

ELSE IF (@Catalog_Value <> '')
SET @Err_Description = 'CatalogID ' + @CatalogID + ' is not approved to production.'

ELSE IF @RequestedQty = NULL
SET @Err_Description = 'RequestedQty is empty.'

ELSE IF ISNUMERIC(@RequestedQty)=0
SET @Err_Description = 'Requested quantity must be a numeric value.'

ELSE IF ISNUMERIC(@Priority)=0 AND @Priority <> ''
SET @Err_Description = 'Priority must be a numeric value.'

IF @Err_Description <> ''
GOTO Err_Handler

/*****************************************************************/
/** Read PO information from xml and insert into tbl_PO table **/
/*****************************************************************/
INSERT INTO tbl_PO
([POUID]
,[POID]
,[RouteID]
,[CatalogID]
,[RequestedQty]
,[Status]
,[LotID]
,[Priority]
,[Created]
,[AvailTime]
,[StartTime]
,[EndTime]
,[UserID]
,[EndUserID])
VALUES( NewID(),
@POID,
@RouteID,
@CatalogID,
convert(decimal, @RequestedQty),
'Created',
@LotID,
convert(int, @Priority),
GetDate(),
NULL ,
NULL ,
NULL ,
@UserID ,
NULL )

SELECT t.n.value('@ID', 'VARCHAR(100)') AS TagID,
t.n.value('.', 'VARCHAR(200)') AS TagValue
INTO #TMP2
FROM @XMLdoc.nodes('POInfo/PO') AS p(n)
CROSS APPLY n.nodes('POTags/Tag') AS t(n)
WHERE p.n.value('POID[1]', 'INT') = @POID

/*****************************************************************/
/** Loop through association tags and check if they exist **/
/** in xml tags. **/
/** If it exists insert into tbl_d_PO with the value from xml. **/
/** Else insert into tbl_d_PO with the default value. **/
/*****************************************************************/
DECLARE @TaglinkUID varchar(255),
@TagDefId varchar(50),
@ControlType varchar(50),
@XmlValue varchar(255),
@Value varchar(255),
@Route varchar(50),
@MinValue varchar(255),
@MaxValue varchar(255),
@SQL varchar(255),
@Rows int

FETCH FIRST FROM Tags_Cursor
INTO @TaglinkUID, @TagDefId, @Value, @ControlType, @MinValue, @MaxValue, @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
--IF EXISTS(Select ID From #TMP2 Where ID = @TagDefId )
Select @Rows=Count(TagID) From #TMP2 Where TagID = @TagDefId
IF @Rows > 0
BEGIN
Select @XmlValue = TagValue From #TMP2 Where TagID = @TagDefId
--IF (@XmlValue = "") SET @XmlValue = @Value

--------------------------------------------------------------------------------------------------
IF @ControlType = 'Combo Box'
BEGIN
If @XmlValue <> ''
--IF EXISTS (select TagDefId from tbl_TagDefinitions_Members where TagDefId = @TagDefId)
IF @SQL = ''
BEGIN
IF NOT EXISTS (select Item from tbl_TagDefinitions_Members where TagDefId = @TagDefId and Item=@XmlValue)
SET @Err_Description = 'Value ' + @XmlValue + ' does not exist for Tag ' + @TagDefId + ' in PO ' + @POID + '.'
END
ELSE
BEGIN
declare @fields varchar(100),
@first varchar(50)

--Get the first field name
set @fields = substring(@SQL,charindex(' ',@SQL)+1,charindex('from',@SQL)-charindex(' ',@SQL)-2)
if charindex(',',@fields) > 0
set @first = substring(@fields,0,charindex(',',@fields))
else
set @first = @fields

--Add that filed to WHERE clause
if substring(@SQL,charindex('where',@SQL),5) = 'where'
set @SQL = @SQL + ' and ' + @first + ' = ' + char(39) + @XmlValue + char(39)
else
set @SQL = @SQL + ' where ' + @first + ' = ' + char(39) + @XmlValue + char(39)

set @SQL = replace(@SQL,'from', 'into #tmp from')

print @SQL

execute (@SQL)

if (@@ROWCOUNT = 0)
SET @Err_Description = 'Value ' + @XmlValue + ' does not exist for Tag ' + @TagDefId + ' in PO ' + @POID + '.'
END
END

ELSE IF @ControlType = 'Check Box'
BEGIN
IF @XmlValue NOT IN ('true','false','1','0')
SET @Err_Description = 'Value ' + @XmlValue + ' does not fit Tag ' + @TagDefId + ' for PO ' + @POID + '.'
END

ELSE IF @ControlType = 'Integer'
BEGIN
IF ISNUMERIC(@XmlValue) = 0 AND @XmlValue <> ''
SET @Err_Description = @TagDefId + ' tag value must be numeric for PO ' + @POID + '.'
Else
BEGIN
SET @XmlValue = CONVERT(varchar(50),FLOOR(@XmlValue))
IF NOT (convert(int,@XmlValue) BETWEEN convert(int,@MinValue) AND convert(int,@MaxValue))
SET @Err_Description = @TagDefId + ' tag value must be in the range [' + @MinValue + '-' + @MaxValue + '] for PO ' + @POID + '.'
END
END

ELSE IF @ControlType = 'Real'
BEGIN
IF ISNUMERIC(@XmlValue) = 0 AND @XmlValue <> ''
SET @Err_Description = @TagDefId + ' tag value must be numeric.'
ELSE IF NOT (convert(decimal,@XmlValue) BETWEEN convert(decimal,@MinValue) AND convert(decimal,@MaxValue))
SET @Err_Description = @TagDefId + ' tag value must be in the range [' + @MinValue + '-' + @MaxValue + '] for PO ' + @POID + '.'
END
--------------------------------------------------------------------------------------------------

IF @Err_Description <> '' GOTO Err_Handler

INSERT INTO tbl_d_PO(TagDefDataUID, TaglinkUID, POID, TagDefId, Value)
VALUES(NEWID(),@TaglinkUID, @POID, @TagDefId, @XmlValue)
END

Else /* If the tag doesn't exists in the xml file */
BEGIN
INSERT INTO tbl_d_PO(TagDefDataUID, TaglinkUID, POID, TagDefId, Value)
VALUES(NEWID(),@TaglinkUID, @POID, @TagDefId, @Value)
END

FETCH NEXT FROM Tags_Cursor
INTO @TaglinkUID, @TagDefId, @Value, @ControlType, @MinValue, @MaxValue, @SQL
END

DROP TABLE #TMP2

FETCH NEXT FROM PO_Cursor
INTO @POID, @RouteID, @CatalogID, @RequestedQty, @LotID, @Priority, @UserID

END

IF @@ERROR <> 0 GOTO Err_Handler

CLOSE Tags_Cursor;
DEALLOCATE Tags_Cursor;

CLOSE PO_Cursor;
DEALLOCATE PO_Cursor;

IF OBJECT_ID('#TMP1','U') is not null
DROP TABLE #TMP1
IF OBJECT_ID('#TMP2','U') is not null
DROP TABLE #TMP2
IF OBJECT_ID('#TMP3','U') is not null
DROP TABLE #TMP3

COMMIT TRANSACTION POTransaction
RETURN

I don't know where it's falling but it does while I'm sending it xml document from asp.net page. When I'm running the same sp from query analyzer it works fine!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 10:34:05

Try to rewrite this part

SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value], 
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
INTO #TMP3
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID
WHERE (tbl_TagLinks.AssociationType = 100)

UNION

SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value],
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID INNER JOIN
tbl_Routes ON tbl_TagLinks.UID1 = tbl_Routes.RouteUID
WHERE (tbl_TagLinks.AssociationType = 101) AND
(tbl_Routes.RouteID=@RouteID)

to this



select * INTO #TMP3 from (
SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value], 
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID
WHERE (tbl_TagLinks.AssociationType = 100)

UNION

SELECT tbl_TagLinks.TaglinkUID,
tbl_TagDefinitions.TagDefId,
isnull(tbl_TagDefinitions.DefaultValue,'') AS [Value],
tbl_TagDefinitions.ControlType,
tbl_TagDefinitions.MinValue,
tbl_TagDefinitions.MaxValue,
tbl_TagDefinitions.SQL
FROM tbl_TagDefinitions INNER JOIN
tbl_TagLinks ON tbl_TagDefinitions.TagDefUID = tbl_TagLinks.TagDefUID INNER JOIN
tbl_Routes ON tbl_TagLinks.UID1 = tbl_Routes.RouteUID
WHERE (tbl_TagLinks.AssociationType = 101) AND
(tbl_Routes.RouteID=@RouteID)
) AS d

N 56°04'39.26"
E 12°55'05.63"

moodi_z
Starting Member

38 Posts

Posted - 2009-06-30 : 01:01:14

Hi,
I solve it by:
alter database myDataBase set arithabort on
(it's not enough to add:
SET ARITHABORT ON
GO
in the top of the sp)
and it gets over the problem.

But I'll add the change that you suggest if it's improve the performance and prevent farther failures!

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources