CODEWITHSUNDEEP
azure-synapse
user15488631
user15488631

Reputation: 95

How to use FOR XML Path in Azure Synapse

Can you please help me will FOR XML PATH works in Azure Synapse. If not what will be the alternative.

Thanks

Upvotes: 4

Views: 5660

Answers (2)

JayWayze
JayWayze

Reputation: 168

In case you you're looking to use FOR XML to prepare e.g. column names for a dynamic pivot query, then the string_agg function is what you are looking for.

string_agg documentation

Upvotes: 4

wBob
wBob

Reputation: 14389

Azure Synapse Analytics dedicated SQL pools (and serverless SQL pools) do not support the FOR XML syntax as per the documentation:

FOR XML not supported in Synapse

The easiest thing to do would be use Azure SQL DB to do your XML processing, eg use a pipeline to land your data there, call a Stored Proc activity to do the processing then retrieve it. Another fun alternative would be to use a Synapse notebook and use Python or Scala to process the XML.

Upvotes: 0

Related Questions