Geek asks and answers

Updating a SQL Server table from XML

I am trying to create a small console application in C # to perform insertions in the product table (ITEMS) in SQL Server 2008 according to the contents of the XML file in FASTEST mode. I already have an .XSD file that contains the correct mappings to the SQL table (which may not be necessary using the approach described below).

Here's a high level of my approach:

  • Read the XML using it to create the table.
  • Execute MERGE in the ITEMS table using the table created from the XML file.
    2a. If the item exists, update it.
    2b. If the item does not exist, insert it.
  • Create a log of only records inserted in XML.

Consider the following ITEMS table and XML file:

POSITIONS

  Item_Id    Name    Price  
     1       Coke     5.00  
     2       Pepsi    3.00  
     3       Sprite   2.00

ITEMS.XML

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>
   <Item>
    <Id>3</Id>
    <Name>Sprite Zero</Name>
    <Price>1.75</Price>
   </Item>

After import, the ITEMS table should look like this:

POSITIONS

  Item_Id    Name         Price  
     1       Coke          5.00  
     2       Pepsi         3.00  
     3       Sprite Zero   1.75  
     5       Mountain Dew  4.50

XML, , ​​ (ITEMS_LOG.XML):

ITEMS_LOG.XML

   <?xml version="1.0" encoding="ISO-8859-1"?>
   <Item>
    <Id>5</Id>
    <Name>Mountain Dew</Name>
    <Price>4.50</Price>
   </Item>

SQLXMLBulkLoad, , , , SQL Server ( , /). SQL, XML, . / !

+3
3

merge output , XML.

, XML in XML out.

create procedure AddItemXML
  @ItemsXML xml,
  @ItemsLogXML xml out
as

declare @Changes table
(
  Item_Id int,
  Name nvarchar(20),
  Price money,
  Action nvarchar(10)
);

merge Items as T
using
  (
    select T.N.value('Id[1]', 'int') as Item_Id,
           T.N.value('Name[1]', 'varchar(20)') as Name,
           T.N.value('Price[1]', 'money') as Price
    from @ItemsXML.nodes('/Item') T(N)
  ) as S
on T.Item_Id = S.Item_Id
when matched then
  update set Name = S.Name, Price = S.Price
when not matched then
  insert (Item_Id, Name, Price) values (S.Item_Id, S.Name, S.Price)
output inserted.Item_Id,
       inserted.Name,
       inserted.Price,
       $action 
  into @Changes;

set @ItemsLogXML = 
  (
    select Item_Id as ID,
           Name,
           Price
    from @Changes
    where Action = 'INSERT'
    for xml path('Item'), type
  );

SE-Data

+4

, , , , . xml , , , ​​ :

DECLARE @xml xml
SET @xml = @xmlCredentials

SELECT
      item.value('@Id', 'int') As ID,
      item.value('@AgentID', 'int') As AgentID,
      item.value('@Username', 'varchar (50)') As Username,
      item.value('@Password', 'varchar (50)') As [Password],
      item.value('@IsDirty', 'bit') As IsDirty,
      item.value('@IsDeleted', 'bit') As IsDeleted
INTO #tmp
FROM @xml.nodes('Credentials/Credential') x(item)

BEGIN TRY
BEGIN TRAN
      INSERT INTO Credentials (AgentID, Username, [Password])
          SELECT
              AgentID, Username, [Password]
          FROM
              #tmp
          WHERE
              ID = 0 AND IsDirty = 1
      UPDATE c
      SET c.[AgentID] = t.AgentID,
          c.[Username] = t.Username,
          c.[Password] = t.[Password]
      FROM
          [dbo].[Credentials] c
      JOIN 
          #tmp t ON t.Id = c.ID
      WHERE
          t.IsDirty = 1 AND t.IsDeleted = 0

      DELETE FROM [dbo].[Credentials]
      FROM [dbo].[Credentials] c
      JOIN #tmp t ON t.Id = c.ID
      WHERE 
          t.IsDirty = 1 AND t.IsDeleted = 1

      COMMIT TRAN
END TRY
BEGIN CATCH

      IF @@TRANCOUNT > 0
            ROLLBACK TRAN

      DECLARE @errorMSG varchar(4000)
      DECLARE @errorSeverity int
      DECLARE @errorState int

      SET @errorMSG = ERROR_MESSAGE()
      SET @errorSeverity = ERROR_SEVERITY()
      SET @errorState = ERROR_STATE()

      RAISERROR (@errorMSG,
                        @errorSeverity, @errorState);

END CATCH

SELECT [ID], [AgentID], [Username], [Password]
FROM [dbo].[Credentials]

xml xml :

// read xml and assign it to string variable
string xml = readxml();

try
{
    string command = "EXEC SaveCredentails '" + xml + "'";
}
catch(Exception e)
{
}
+2

xml SQL Server. ( ). sql upserts, . XML ( , , ).

0

More articles:


All Articles