Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,374 questions
Azure Synapse pipeline parse xml data to rowset
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(150.4, 27%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENM%3C/text%3E%3C/svg%3E)
Nik Ms
1
Reputation point
Hello,
I have source a table where one column has xml data stored in the column as text (nvarchar(max)).
I need parse this xml from every row in the set of columns and rows and load into Azure Synapse SQL dedicated pool. xml functions don't apply here because Azure synapse does not support xml.
Table is something like this:
----------------------------------------------------------------------------------------------
studentId studentName requests
----------------------------------------------------------------------------------------------
1 xxx <Customers>
<row>
<CUSTOMERNO>100</CUSTOMERNO>
<OPERATION>INSERT</OPERATION>
<EMAIL>bill.gates@microsoft.com</EMAIL>
</row>
</Customers>
2 yyy <Customers>
<row>
<CUSTOMERNO>101</CUSTOMERNO>
<OPERATION>INSERT</OPERATION>
<EMAIL>top.dates@Muhammad Nabil .com</EMAIL>
</row>
</Customers>
3 xyz <Customers>
<row>
<CUSTOMERNO>120</CUSTOMERNO>
<OPERATION>INSERT</OPERATION>
<EMAIL>bill.gates@microsoft.com</EMAIL>
</row>
<row>
<CUSTOMERNO>121</CUSTOMERNO>
<OPERATION>DELETE</OPERATION>
<EMAIL>******@xyztool.org</EMAIL>
</row>
<row>
<CUSTOMERNO>122</CUSTOMERNO>
<OPERATION>UPDATE</OPERATION>
<EMAIL>steve.jobs@apple .com</EMAIL>
</row>
</Customers>
----------------------------------------------------------------------------------------------
I tried to use data flow Parse transformation, but for the third row it returns only last element (where CUSTOMERNO is 122). But I need such result table:
----------------------------------------------------------------------------------------------
studentId studentName CUSTOMERNO OPERATION EMAIL
----------------------------------------------------------------------------------------------
1 xxx 100 INSERT bill.gates@microsoft.com
2 yyy 101 INSERT top.dates@Muhammad Nabil .com
3 xyz 120 INSERT bill.gates@microsoft.com
3 xyz 121 DELETE ******@xyztool.org
3 xyz 122 UPDATE steve.jobs@apple .com
----------------------------------------------------------------------------------------------
or such:
----------------------------------------------------------------------------------------------
CUSTOMERNO OPERATION EMAIL
----------------------------------------------------------------------------------------------
100 INSERT bill.gates@microsoft.com
101 INSERT top.dates@Muhammad Nabil .com
120 INSERT bill.gates@microsoft.com
121 DELETE ******@xyztool.org
122 UPDATE steve.jobs@apple .com
----------------------------------------------------------------------------------------------
Please could someone tell me how to parse the xml text into a set of strings?
Thanks in advance.
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 26,586 Reputation points2022-12-26T14:57:25.683+00:00 @Nik Ms ,
You can shred XML into relational/rectangular format upon retrieval (SELECT ...) from the table via T-SQL and XQuery.
-
Nik Ms 1 Reputation point
2022-12-26T15:55:52.51+00:00 I agree with you. But, unfortunately, XML is not supported by Azure Synapse. That's why I'm trying to process XML by pipeline/dataflow.
-
ShaikMaheer-MSFT 38,546 Reputation points Microsoft Employee Moderator2022-12-28T06:35:13.687+00:00 Hi @Nik Ms ,
Thank you for posting query in Microsoft Q&A Platform. I understand that you want to take xml from one of column in table and load it as columns in dedicated SQL table. But, its not clear that what is the source here? Is it Azure SQL Db or On-prem SQL or anything else?
-
Nik Ms 1 Reputation point
2022-12-28T07:30:50.447+00:00 Sources are csv files where one field contents xml text data - as is on first code.
Then data from csv files are loaded into tables on the Azure Synapse SQL Dedicated pool
After that we need to parse xml on all rows, transform them into the rows with columns and put into stage tables (desirable results in second or third tables).
p.s. I'm sorry that it's badly readable, because the first time it was accidentally published inadequately, and changes in the posts here are unfortunately impossible (?).
Sign in to comment
Sign in to answer