Passing xml string parameter to SQL Server stored procedure

Question

I am passing an xml string to stored procedure in SQL Server for inserting 10000 records to my table. In this when I call this stored procedure. Want to check the SQL Server table with that xml string which I am passing, if the record exists I don't want to insert, if it is new record that record alone have to insert.Give some solution. Thanks.

  ALTER procedure [dbo].[SP_CMSUSER1]
      (@xmlString ntext)
    as
    begin

      DECLARE @idoc INT
      DECLARE @data nvarchar(100)

      EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

    INSERT INTO dbo.Seg_RecipientsTemp (ContactID,first_name,last_name,company,email,last_updated)
    SELECT ContactID,
    first_name,
    last_name,
    company,
    email,
    last_updated FROM OPENXML(@idoc,

    '/NewDataSet/ContactData', 6)

    WITH

    (ContactID int ,
    first_name nvarchar(50), 
    last_name  nvarchar(50), 
    company    nvarchar(max),
    email nvarchar(100), 
    last_updated datetime 



    )
    end

My Xml is:

 <NewDataSet>
  <Table>
    <ContactID>2</ContactID>
    <last_name>klklk</last_name>
  </Table>
  <Table>
    <ContactID>4</ContactID>
    <first_name>k</first_name>
    <last_name>kk</last_name>
    <company>k</company>
  </Table>
  <Table>
    <ContactID>6</ContactID>
    <first_name>naveen</first_name>
    <last_name />
    <company>inno</company>
  </Table>
  <Table>
    <ContactID>7</ContactID>
    <first_name>sridar</first_name>
    <last_name />
    <company>mahindara</company>
  </Table>
  <Table>
    <ContactID>1</ContactID>
    <first_name>terst</first_name>
  </Table>
  <Table>
    <ContactID>2</ContactID>
    <first_name />
    <last_name>ask</last_name>
    <company />
  </Table>
</NewDataSet>

Vereen

It would be helpful to see a sample XML and get an explanation of what you want to extract from it! – Clemmy

i want to check if the contactid exists in sql table with xml string,if already exists i dont want insert ,new record only have to insert,,i need If not exists(select * from dbo.Seg_RecipientsTemp where Contact_Id=(in this i need check with xml conatacid) – Vereen

Do you want to INSERT the records for ContactID's that don't exist in dbo.Seg_RecipientsTemp but exist in XML data? Do you want to UPDATE the data in dbo.Seg_RecipientsTemp for ContactID's that do exist in XML data? – Stylus

What version of SQL Server? Every version since 2005 has supported a proper xml data type, and it would be far better to use that if possible – Vallonia

no I dont want to update existing,i dont do anything with that,if the new record comes i need to insert.i dont want update for existing records – Vereen

I am using 2008,i dont know my source table value is NULL now – Vereen

Answer 1

Define your stored procedure to take a parameter of type XML (don't use ntext anymore! It's deprecated). And don't use the sp_ prefix for your stored procedures - it's a reserved prefix for internal use by Microsoft and causes performance degradation - use something else! (or don't use any prefix at all)

 ALTER procedure [dbo].InsertCmsUser
      @xmlString XML
 AS
     ......

Try this (using the native XQuery methods in SQL Server 2005 and newer, instead of the rather messy OPENXML interface....):

;WITH CTE AS
(
    SELECT
        ContactID = XTbl.value('(ContactID)[1]', 'int'),
        FirstName = XTbl.value('(first_name)[1]', 'varchar(50)'),
        LastName = XTbl.value('(last_name)[1]', 'varchar(50)'),
        Company = XTbl.value('(company)[1]', 'varchar(50)')
    FROM 
        @input.nodes('/NewDataSet/Table') AS XD(XTbl)
)
INSERT INTO 
    dbo.Seg_RecipientsTemp (ContactID, first_name, last_name, company, last_updated)
    SELECT 
        ContactID,
        FirstName,
        LastName,
        Company,
        GETDATE()
    FROM
        CTE
    WHERE
        NOT EXISTS (SELECT * FROM dbo.Seg_RecipientsTemp WHERE ContactID = CTE.ContactID)

I didn't find any email attribute in your XML - not sure where you want to get that from ....

Update: ok, so you seem to also have <last_updated> elements in your real XML ....

<last_updated>2012-09-12T22:59:10.813+05:30</last_updated>

This looks like a DATETIMEOFFSET to me - since it has the +05:30 time zone addition.

In that case, use this code instead:

;WITH CTE AS
(
    SELECT
        ContactID = XTbl.value('(ContactID)[1]', 'int'),
        FirstName = XTbl.value('(first_name)[1]', 'varchar(50)'),
        LastName = XTbl.value('(last_name)[1]', 'varchar(50)'),
        Company = XTbl.value('(company)[1]', 'varchar(50)'),
            LastUpdated = XTbl.value('(last_updated)[1]', 'datetimeoffset')
    FROM 
        @input.nodes('/NewDataSet/Table') AS XD(XTbl)
)
INSERT INTO 
    dbo.Seg_RecipientsTemp (ContactID, first_name, last_name, company, last_updated)
    SELECT 
        ContactID,
        FirstName,
        LastName,
        Company,
        LastUpdated
    FROM
        CTE
    WHERE
        NOT EXISTS (SELECT * FROM dbo.Seg_RecipientsTemp WHERE ContactID = CTE.ContactID)

Clemmy

Thanx for your response.Sorry email id there i didnot added in xml for testing purpose,i got an error i got another problem Conversion failed when converting datetime from character string for lastupdated field,,in xml i get below tag <last_updated>2012-09-12T22:59:10.813+05:30</last_updated> ..please give some solution – Vereen

@Kamalakannan.M: it would have been nice to see such a <last_updated> tag in the sample you posted! See my updated response – Clemmy

XML parsing: line 76442, character 53, illegal xml character – Vereen

Answer 2

I'm not allowed to comment yet, but hoping to help even though this isn't a real answer. This page offers a great detailed explanation:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/ (not affiliated)

The nodes() method The nodes() method can be a bit more slippery to understand than the other XML methods. To begin with, rather than returning XML or scalar values, the nodes() method returns what is essentially a table that includes one column. That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause. It also means that, when you call the nodes() method, you must assign a table alias and column alias to the rowset view returned by the method, as shown in the following syntax:

DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)

Rapport

Answer 3

Simple try this

        DECLARE @File varchar(MAX)='<?xml version="1.0" encoding="utf-8" ?><UpSellPremium><Premium><TransactionID>ICM_AUG_18_963589</TransactionID><UpSellPr>15009</UpSellPr></Premium><Premium><TransactionID>ICM_AUG_18_1330170</TransactionID><UpSellPr>18900</UpSellPr></Premium></UpSellPremium>'
        DECLARE @idoc int                              
        DECLARE @doc xml
                          
    SET @doc = @File                              
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc                               

        SELECT * 
        INTO #Temp_UpSellPr 
        FROM OpenXML(@idoc,'//Premium')                              
        WITH 
        (
        XMLTransactionID VARCHAR(50) 'TransactionID',
        XMLUpSellPr FLOAT 'UpSellPr'
        )

   SELECT * FROM #Temp_UpSellPr

Froma