Share via

How to parse XML data enclosed in nvarchar datatype using Azure datafactory

Shiva 0 Reputation points
2023-04-24T15:36:50.7+00:00

Hello, I have xml data enclosed in nvarchar datatype, I need respective data to be mapped to new columns Catalog Name and RequireApprove, Could anyone help on this using Azure Data Factory. sample xml data,  <catalogs><Catalog
Name="NEWYORK"
RequireApprove="True"/>
</catalogs>   Thank you

Azure Synapse Analytics
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.
4,696 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sedat SALMAN 13,345 Reputation points
    2023-04-26T06:24:38.6733333+00:00

    In Azure Data Factory, you can use the Mapping Data Flows feature to parse XML data stored in an nvarchar column and map it to new columns.

    • Create a new pipeline in Azure Data Factory.
      • Add Copy Data Activity
    • Set up the source dataset:
      • Create a new dataset for your source data (e.g., SQL Server, Azure Blob Storage, etc.).
      • Set the data type of the column containing the XML data as nvarchar.
      • Import the schema or enter it manually, making sure the column containing the XML data is included.
    • Set up the sink dataset:
      • Create a new dataset for your destination data (e.g., SQL Server, Azure Blob Storage, etc.).
      • Define the schema with the new columns "Catalog Name" and "RequireApprove" along with their respective data types (e.g., nvarchar for Catalog Name and bit for RequireApprove).
    • Configure the mapping:
      • In the "Copy Data" activity, click the "Mapping" tab.
      • For each new column ("Catalog Name" and "RequireApprove"), click "Add dynamic content" and use the following expressions:
        • Catalog Name: @XPath(xml(columnName), 'string(/catalogs/Catalog/@Name)')
        • RequireApprove: @XPath(xml(columnName), 'string(/catalogs/Catalog/@RequireApprove)') Replace columnName with the name of the column containing the XML data in your source dataset.
    • Configure the settings of the "Copy Data" activity as needed, such as concurrency, fault tolerance, etc.
    • Publish the changes to your Azure Data Factory and trigger the pipeline to run. This will parse the XML data from the nvarchar column in the source dataset and map the Catalog Name and RequireApprove attributes to the corresponding columns in the sink dataset.
  2. ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee
    2023-05-10T09:52:02.6733333+00:00

    Hi Shiva,

    Thank you for posting query in Microsoft Q&A Platform.

    From the error screenshots, it seems its type mismatch. That means your expression may expecting some type but you are supplying some other type. Kindly check the types of values which you are passing in to xpath() and xml() functions.

    Check below links to understand about these functions with examples.

    xpath(): https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#xpath

    xml(): https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#xml

    Hope this helps. Please let me know how it goes.

    Please consider hitting Accept Answer button. Accepted answers help community as well.