I created a table with xml data type column in azure sql database and inserted values into that.
students:
CREATE TABLE dbo.students (
studentId INT NOT NULL PRIMARY KEY,
studentName VARCHAR(20) NOT NULL,
request XML NOT NULL
)
insert into students(studentId,studentName,request)
values
(1,'XXX','<Customers><row><CUSTOMERNO>100</CUSTOMERNO><OPERATION>INSERT</OPERATION><EMAIL>[email protected]</EMAIL></row> </Customers>'),
(2,'YYY','<Customers><row><CUSTOMERNO>101</CUSTOMERNO><OPERATION>INSERT</OPERATION><EMAIL>[email protected]</EMAIL></row></Customers>'),
(3,'ZZZ','<Customers><row><CUSTOMERNO>12</CUSTOMERNO><OPERATION>INSERT</OPERATION><EMAIL>[email protected]</EMAIL></row><row>
<CUSTOMERNO>947</CUSTOMERNO><OPERATION>UPDATE</OPERATION><EMAIL>[email protected]</EMAIL></row><row>
<CUSTOMERNO>947</CUSTOMERNO><OPERATION>DELETE</OPERATION><EMAIL>[email protected]</EMAIL></row></Customers>');
Image for reference:
I created another table and retrieve the data from the xml data type column into that table using below code
CREATE TABLE dbo.studentTable (
studentId INT NOT NULL,
studentName VARCHAR(20) NOT NULL,
customerno INT NOT NULL,
operation VARCHAR(20) NOT NULL,
email VARCHAR(100) NOT NULL
)
INSERT INTO dbo.studentTable ( studentId,studentName, customerno, operation, email )
SELECT
s.studentId,
s.studentName,
c.c.value( '(CUSTOMERNO/text())[1]', 'INT' ) customerno,
c.c.value( '(OPERATION/text())[1]', 'VARCHAR(20)' ) operation,
c.c.value( '(EMAIL/text())[1]', 'VARCHAR(100)' ) email
FROM dbo.students s
CROSS APPLY s.request.nodes('Customers/row') c(c)
Output of table:
I did this in azure SQL database because azure synapse dedicated pool is not supported for xml data type.
we can copy above table to azure synapse dedicated pool using azure synapse pipeline copy activity.
I created dedicated SQL pool in azure synapse and created pipeline and performed copy activity using below procedure:
created azure SQL database dataset using azure SQL database linked service.
azure SQL database linked service:
Azure SQL database dataset:
Source of copy activity:
I created table in SQL pool- using below code:
CREATE TABLE dbo.studentTable (
studentId INT NOT NULL,
studentName VARCHAR(20) NOT NULL,
customerno INT NOT NULL,
operation VARCHAR(20) NOT NULL,
email VARCHAR(100) NOT NULL
)
Creating SQL dedicated pool linked service:
search for synapse dedicated pool in linked service options.
Image for reference:
select and click on continue and fill required details and click on OK.
Image for reference:
I gave sink as synapse dedicated pool database by enabling bulk insert option.
I debug the pipeline It run successfully.
Image for reference:
The table is copied successfully into dedicated pool.
Image for reference:
