Updating xml in sql dating an ambitious guy

Rated 3.92/5 based on 833 customer reviews

CREATE TABLE #Customers( [Customer ID] [nchar](5) NOT NULL, [Company Name] [nvarchar](40) NOT NULL, [Contact Name] [nvarchar](30) NULL, [Contact Title] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [Postal Code] [nvarchar](10) NULL, [Country] [nvarchar](15) NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL, CONSTRAINT [PK_Customers1] PRIMARY KEY CLUSTERED ( [Customer ID] ASC ) ); -- Inserting all the rows from xml to temproary table using OPENROWSET -- Thanks to INTO #Customers (Customer ID, Company Name, Contact Name, Contact Title, [Address], City, Region, Postal Code, Country, Phone, Fax) SELECT X.

The XML file looks as below: CREATE PROCEDURE SP_Import Customer Data_XML AS BEGIN -- Creating a Temproary Table for importing the data from csv file.

Fax) WHEN MATCHED -- If not matched then UPDATE THEN UPDATE SET Target Table.

The SQL SP would be: CREATE PROCEDURE SP_Import Customer Data AS BEGIN CREATE TABLE #Temp City( [Id] [int] IDENTITY(1,1) NOT NULL, [Country] [varchar](50) NULL, [State] [varchar](50) NULL, [City] [varchar](50) NULL, [Remarks] [varchar](200) NULL ) ON [PRIMARY]; BULK INSERT #Temp City FROM 'C:\Blog\test1.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); MERGE Temp City AS Target Table USING #Temp City AS Source Table ON (Target Table. Id) WHEN NOT MATCHED BY TARGET THEN INSERT (Country, State, City, Remarks) VALUES(Source Table.

It can contain any number which is not the existing record id would be fine.

So, there is no need to include exact id in the csv file.

Implementation for XML data: Let us take the same example and explore using XML file as input.

USING #Customers AS Source Table -- Records from the temproary table (records from csv file). THEN INSERT (Customer ID, Company Name, Contact Name, Contact Title, [Address], -- then INSERT the record City, Region, Postal Code, Country, Phone, Fax) VALUES(Source Table. It is important to note is, the autoincrement id will be consider to compare with already inserted records id and not for new records. The csv file would be CREATE PROCEDURE SP_Import Customer Data AS BEGIN CREATE TABLE #Temp City( [Country] [varchar](50) NULL, [State] [varchar](50) NULL, [City] [varchar](50) NULL, [Remarks] [varchar](200) NULL ) ON [PRIMARY]; BULK INSERT #Temp City FROM 'C:\Blog\test2.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); MERGE Temp City AS Target Table USING #Temp City AS Source Table ON (Target Table. The table script would be: 1, India, Tamil Nadu, Chennai, 2, India, Tamil Nadu, Coimbatore, 3, India, Tamil Nadu, Madurai,testremarks 4, India, Tamil Nadu, Vellore, 5, India, Karnataka, Bangalore, 6, India, Karnataka, Mangalore, 7, India, Gujarat, Ahmedabad, Note: Add an empty line at the end. So to identity which record to update (if exist) or insert (if not exist) - we need to include that id also in csv file (included as first column). It is important to note is the auto-increment id is not consider anywhere. MERGE Customers AS Target Table -- Inserting or Updating the table. Customer ID) -- Defining condition to decide which records are alredy present WHEN NOT MATCHED BY TARGET -- If the records in the Customer table is not matched? Customer.query('Customer ID').value('.', 'nchar(5)'), X. Customer.query('Company Name').value('.', 'nvarchar(40)'), X. Customer.query('Contact Name').value('.', 'nvarchar(30)'), X. Customer.query('Contact Title').value('.', 'nvarchar(30)'), X. Customer.query('Address').value('.', 'nvarchar(60)'), X. Customer.query('City').value('.', 'nvarchar(15)'), X. Customer.query('Region').value('.', 'nvarchar(15)'), X. Customer.query('Postal Code').value('.', 'nvarchar(10)'), X. Customer.query('Country').value('.', 'nvarchar(15)'), X. Customer.query('Phone').value('.', 'nvarchar(24)'), X. Customer.query('Fax').value('.', 'nvarchar(24)') FROM (SELECT CAST(x AS XML) FROM OPENROWSET(BULK 'D:\Import Data\XMLData1.xml', SINGLE_BLOB) AS T(x) ) AS T(x) CROSS APPLY x.nodes('Customers/Customer') AS X(Customer); -- Selecting the records from temproary table. -- SELECT * FROM #Customers; -- By using MERGE statement, inserting the record if not present and updating if exist. Contact Title, Source Table.[Address], Source Table.

Leave a Reply