SQL Column Containing XML

[dcsearle]

Hi,

I've a SQL table as a datasource and a lakehouse as a destination. The SQL table has a column which is storing XML data (for extensibility, it's a set of custom fields we can add to an application). 
I'm looking for the best way of extracting specific fields from the XML column. I've tried simply loading the column and then querying it via the SQL analytics end point, but it doesn't seem to support querying using XML methods.

Can anyone advise on what is a valid or better approach for achieving this? Is it better to do it during the load as part of the pipleine or am I missing an alternative option?

Thanks,

David

[Anonymous]

Hi @dcsearle 

Of course, it's a good idea to prepare the data as a query or view before loading it into the pipeline.

​With this approach, you can leverage SQL's query capabilities to transform and clean data before it enters the pipeline.

Steps to prepare data using SQL view:

​Create a view to extract XML fields. Use SQL to create a view that extracts a specific field from an XML column.

​You can query this view for structured data.

​Load the data from this view into the lakehouse.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dcsearle]

I'm guessing I could prepare the data as a query or view and make use of the SQL functionality there prior to loading it into the pipeline...

Let me know if any other suggestions.

[smpa01]

@dcsearle  can you please explain what syntax you tried that did not work. I have a similar rask in few days, I can learn from you. With a regular sql server (opposed to azure) usually xml cam be handled by xpath syntax such as value, nodes combined with cross apply