title: sql for how to check if a record exists when inserting from xml
body: <p>I am inserting records from some XML.  I need to check if the record already exists and insert if it doesn't and update if it does.<br>
  This is what i have but there are multiple records returned from the subquery.</p>
  
  <pre><code>   ALTER PROCEDURE [dbo].[InsertOrdersFromXML] (
   @xmlData XML 
   )
   AS
   begin tran /* default read committed isolation level is fine */
  declare @orderId int
  set @orderId = (SELECT x.XmlCol.value('OrderNumber[1]', 'int') FROM @xmlData.nodes('//Order') AS x(XmlCol))
  
  if not exists (SELECT StoreorderId FROM Orders with (updlock, rowlock, holdlock) where StoreOrderId = @orderId)
      /* insert */
  
  INSERT INTO [dbo].[Orders]
             ([StoreOrderId]
             ,[CustomerId]
             ,[OrderDate]
  
  SELECT
  x.XmlCol.value('OrderNumber[1]', 'int') as 'StoreOrderId',
  x.XmlCol.value('CustomerID[1]', 'int') as 'CustomerId',
  x.XmlCol.value('OrderDate[1]', 'datetime') as ' OrderDate',
  
  FROM @xmlData.nodes('//Order') AS x(XmlCol)
  
  INSERT INTO [dbo].[OrderLines]
             ([OrderId]
             ,[StoreOrderId]
             ,[ProductId]
             ,[Quantity]
             ,[ChosenColour]
             ,[ChosenSize]
             ,[ShoppingCartRecID])
  
  select (select OrderId from Orders with (updlock, rowlock, holdlock) where storeorderid = x.XmlCol2.value('OrderNumber[1]', 'varchar(25)')),
  
  x.XmlCol2.value('OrderNumber[1]', 'varchar(25)') as ' OrderNumber',
      x.XmlCol2.value('ProductID[1]', 'varchar(25)') as ' ProductID',
      x.XmlCol2.value('Quantity[1]', 'varchar(25)') as ' Quantity',
      x.XmlCol2.value('ChosenColor[1]', 'varchar(25)') as ' ChosenColor',
      x.XmlCol2.value('ChosenSize[1]', 'varchar(25)') as ' ChosenSize',
      x.XmlCol2.value('ShoppingCartRecID[1]', 'varchar(25)') as ' ShoppingCartRecID'
      FROM @xmlData.nodes('//Order/OrderItems/Item') AS x(XmlCol2)
  
  commit
  </code></pre>
  
  <p>also, how can I add the update part?</p>