xml values are not inserting into the table [closed]

Posted on

Question :

Here is my code:

ALTER PROCEDURE sp_Insertxmldata
    @xml xml
AS
BEGIN
    declare @hdoc as int

    EXEC sp_xml_preparedocument @hdoc output,@xml 

    Insert into tbllogin([user_name],mobile,[Password])
    select [USER_NAME],mobile,[password] 
    FROM openxml(@hdoc, '/NewDataSet/table', 2) 
    with 
    (
        [user_name] varchar(50)'@username',
        mobile varchar(50) '@mobile',
        [password] varchar(15) '@password'
    )

    EXEC sp_xml_removedocument @hdoc  

END
GO

The xml is:

<NewDataSet>
  <Table1>
    <user_name></user_name>
    <password></password>
    <mobile></mobile>
  </Table1>
</NewDataSet>

Answer :

As you are using the XML datatype, you must be using SQL Server 2005 or above. Therefore you can use the methods of the XML datatype (eg .nodes, .value, .query) to read the XML. For such a small piece of XML performance is likely to be the same or better than OPENXML, eg

DECLARE @xml XML

SET @xml = '<NewDataSet>
  <Table1>
    <user_name></user_name>
    <password></password>
    <mobile></mobile>
  </Table1>
</NewDataSet>'

--INSERT INTO ...
SELECT
    t.c.value('(user_name/text())[1]', 'VARCHAR(50)' ),
    t.c.value('(mobile/text())[1]', 'VARCHAR(50)' ),
    t.c.value('(password/text())[1]', 'VARCHAR(15)' )
FROM @xml.nodes('NewDataSet/Table1') t(c)

Remember when dealing with XML, things are generally case-sensitive, so I’m using ‘Table1’ rather than ‘table’ as per your original post.

Leave a Reply

Your email address will not be published.