April 25, 2014 at 5:09 am
HI
I have two tables using Table1 XML column I need to Update Table 2 columns .
Following is the Code.
CREATE TABLE Table1(
[ID] int,
[Version] int,
[Start_Date] Date,
[End_Date] Date,
[XML_Uncompressed] [xml] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Create Table Table2(
[No] int,
[Version] int,
[Start_date] Date,
[End_Date] Date,
[Age] int,
[Name] varchar(50),
[City] varchar(50),
[Postcode] varchar(50)
)
Insert into Table1
(Id,
version,
Start_Date,
End_Date,
XMl_UnCompressed)
values (
3
,2
,'2005-04-12'
,'2012-04-12'
,'<Details><Age> 35</Age><Name>MR</Name><City>BGLRE</City></Details>'
I Need to Get values from XML_uncompressed column of Table1 and insert into Age,Name,City columns of Table2
Please help on how to insert / Update the values using XML column values
April 25, 2014 at 7:39 am
First thing you will need to do is to be able to select those values. Try playing with this
select xml_uncompressed.value ('(/Details/Age)[1]', 'int'),
xml_uncompressed.value ('(/Details/Name)[1]', 'varchar(20)'),
xml_uncompressed.value ('(/Details/City)[1]', 'varchar(20)')
from Table1
After you can select the data, then the insert/update should follow pretty cleanly.
April 25, 2014 at 8:03 am
Another way of doing this:
😎
SELECT
T1.ID
,DET.AILS.value('Age[1]','INT') AS Age
,DET.AILS.value('Name[1]','NVARCHAR(128)') AS Name
,DET.AILS.value('City[1]','NVARCHAR(128)') AS City
FROM dbo.Table1 T1
OUTER APPLY T1.XML_Uncompressed.nodes('Details') AS DET(AILS)
April 26, 2014 at 7:12 am
How can I update it
April 26, 2014 at 7:59 am
greeshatu (4/26/2014)
How can I update it
Complete listing using the MERGE statement
😎
USE tempdb;
GO
CREATE TABLE Table1(
[ID] int,
[Version] int,
[Start_Date] Date,
[End_Date] Date,
[XML_Uncompressed] [xml] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Create Table Table2(
[No] int,
[Version] int,
[Start_date] Date,
[End_Date] Date,
[Age] int,
[Name] varchar(50),
[City] varchar(50),
[Postcode] varchar(50)
)
Insert into Table1
(Id,
version,
Start_Date,
End_Date,
XMl_UnCompressed)
values (
3
,2
,'2005-04-12'
,'2012-04-12'
,'<Details><Age> 35</Age><Name>MR</Name><City>BGLRE</City></Details>')
MERGE Table2 AS DEST
USING (
SELECT
T1.ID
,DET.AILS.value('Age[1]','INT') AS Age
,DET.AILS.value('Name[1]','NVARCHAR(128)') AS Name
,DET.AILS.value('City[1]','NVARCHAR(128)') AS City
FROM dbo.Table1 T1
OUTER APPLY T1.XML_Uncompressed.nodes('Details') AS DET(AILS)
) AS SRC
ON SRC.ID = DEST.No
WHEN MATCHED THEN
UPDATE
SET Age = SRC.Age
,Name = SRC.Name
,City = SRC.City
WHEN NOT MATCHED THEN
INSERT
(
No
,Age
,Name
,City
)
VALUES
(
SRC.ID
,SRC.Age
,SRC.Name
,SRC.City
);
SELECT * FROM Table2;
DROP TABLE Table1;
DROP TABLE Table2;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply