使用存储过程将XML中的Html数据保存到sql

Here is the Xml data of table i am passing from front end to stored procedure.

这是我从前端传递到存储过程的表的Xml数据。

<ArrayOfUserData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <UserData>
             <Name>Dovyan<Name />
             <id>434556464<id />
     </UserData>
     <UserData>
             <Name>Alex<Name />
             <id>12345767<id />
    </UserData>
</ArrayOfUserData>

This is the above xml i am passing to stored procedure as @in_params. Here is the stored procedure.

这是上面的xml我将作为@in_params传递给存储过程。这是存储过程。

USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [DATABASE].[sp_SaveUsertable]

(
   @in_params AS XML
)
AS

BEGIN
select
     T.x.value('./Name[1]','varchar(6)') as [Name] ,
     T.x.value('./id[1]','varchar(75)') as [id] 

into #Temp
from @in_params.nodes('/ArrayOfUserData/UserData') as T(x);


BEGIN TRANSACTION

INSERT INTO [dbo].[usertable](Name,id)
SELECT Name,id FROM #Temp AS T

COMMIT TRANSACTION
drop table #Temp;

END

Can someone please tell what am i possibly doing wrong. The data is not getting saved in the database table.

有人可以告诉我可能做错了什么。数据未保存在数据库表中。

1 个解决方案

#1

Your query is correct but your XML data is not formatted properly, The closing tags for Name and ID are self-closing tags, even though you have start tag and data in between. They need to be proper closing tags and not self-closing tags. see below:

您的查询是正确的,但您的XML数据格式不正确,名称和ID的结束标记是自动关闭标记,即使您在其间有开始标记和数据。他们需要正确关闭标签而不是自动关闭标签。见下文:

<ArrayOfUserData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <UserData>
             <Name>Dovyan<Name />  --<-- This closing tag should be </Name>
             <id>434556464<id />   --<-- This closing tag should be </id>
     </UserData>
     <UserData>
             <Name>Alex<Name />    --<-- This closing tag should be </Name> 
             <id>12345767<id />    --<-- This closing tag should be </Name>
    </UserData>
</ArrayOfUserData>

If you fixed your XML your stored procedure should work. But one thing I would change in you procedure is the use of Temp table, it is no needed and just simply do an insert into the table from your xml parameter. Something like....

如果修复了XML,则存储过程应该可以正常工作。但是我会在你的程序中改变一件事是使用Temp表,它是不需要的,只需从你的xml参数中插入到表中即可。就像是....

BEGIN TRANSACTION

    INSERT INTO [dbo].[usertable](Name,id)
    select
          T.x.value('./Name[1]','varchar(6)') as [Name] 
         ,T.x.value('./id[1]','varchar(75)') as [id] 
    from @Xml.nodes('/ArrayOfUserData/UserData') as T(x);

COMMIT TRANSACTION