将XML字符串参数传递给SQL Server存储过程

Question

将XML字符串参数传递给SQL Server存储过程

sql-serversql-server-2008t-sql

3

我正在将一个包含10000条记录的XML字符串传递给SQL Server存储过程，用于将这些记录插入到我的表中。当我调用这个存储过程时，希望检查SQL Server表与我传递的XML字符串是否存在相同的记录，如果已存在，则不需要插入，如果是新记录，则仅插入该记录。请提供一些解决方案。谢谢。

  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

我的Xml如下：

 <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>

- Kamalakannan Manivel

3

能否提供一个XML样例并解释一下你希望从中提取哪些内容，这将会很有帮助！ - marc_s

我想检查XML字符串中的contactid是否存在于SQL表中，如果已经存在，则不需要插入新记录，只需插入新记录。我需要执行以下操作：如果不存在（select * from dbo.Seg_RecipientsTemp where Contact_Id =（在此处我需要使用xml conatacid进行检查））。 - Kamalakannan Manivel

你想要插入ContactID记录到dbo.Seg_RecipientsTemp中，但这些记录在XML数据中是不存在的吗？你想要更新dbo.Seg_RecipientsTemp中已存在于XML数据中的ContactID吗？ - Ivan Golović

SQL Server的哪个版本？自2005年以来的每个版本都支持适当的“xml”数据类型，如果可能的话最好使用它。 - Damien_The_Unbeliever

不，我不想更新现有记录，我对此不做任何操作。如果有新的记录出现，我需要插入它们。我不希望更新现有记录。 - Kamalakannan Manivel

我正在使用2008，我不知道我的源表值现在是否为NULL。 - Kamalakannan Manivel

2个回答

0

我现在还不能发表评论，但是希望能够帮到你，即使这不是一个真正的答案。这个页面提供了非常详细的解释：

https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/ （与我们无关）

节点() 方法节点() 方法可能比其他 XML 方法更难理解。首先，它不是返回 XML 或标量值，而是返回一个包括一列的表格。这意味着只有那些能够处理行集视图的语句部分（例如FROM子句）才应该使用该方法。同时，当调用节点() 方法时，必须为方法返回的行集视图指定表别名和列别名，如下面的语法所示：

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

- squicc

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- marc_s · Accepted Answer

定义您的存储过程以接受类型为XML的参数（不再使用ntext！它已被弃用）。而且，请勿在存储过程中使用sp_前缀 - 这是Microsoft内部使用的保留前缀，会导致性能下降 - 使用其他内容！（或根本不使用任何前缀）

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

尝试这样做（使用 SQL Server 2005 及更高版本中的本地 XQuery 方法，而不是相当混乱的 OPENXML 接口...）：

;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)

我在你的XML中没有找到任何email属性 - 不确定你想从哪里获取它....

更新: 好的，所以你似乎在你的真实XML中也有<last_updated>元素....

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

这看起来像是一个带有时区偏移量的DATETIMEOFFSET - 因为它有+05:30的时区加法。

在这种情况下，请改用以下代码：

;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)