%3CLINGO-SUB%20id%3D%22lingo-sub-1420562%22%20slang%3D%22en-US%22%3EIngesting%20XML%20data%20into%20Azure%20Data%20Explorer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1420562%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20customers%20wonder%2C%20what's%20the%20right%20way%20of%20ingesting%20XML%20data%20into%20Azure%20Data%20Explorer%3F%20Well%2C%20there's%20no%20native%20support%20for%20XML%20format%20yet%2C%20but%20there's%20built-in%20function%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fparse-xmlfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eparse_xml()%3C%2FA%3E%26nbsp%3Bthat%20accepts%20XML%20string%20and%20returns%20a%20corresponding%20structure%20of%20dynamic%20type%2C%20which%20we%20could%20probably%20use.%20Thus%2C%20here's%20the%20recipe.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20make%20it%20even%20more%20interesting%2C%20let's%20assume%20that%20the%20data%20is%20coming%20as%20JSON%20that%20includes%20a%20string%20field%20containing%20an%20XML%20chunk%20of%20data%20(don't%20ask%20me%20why%2C%20it's%20not%20uncommon%20to%20see%20such%20interesting%20blends%20all%20over%20many%20legacy%20systems)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%5B%0A%20%7B%0A%20%20%22ItemID%22%3A%20%22d9a205b6-9423-450e-bafb-cd643623328d%22%2C%0A%20%20%22ItemName%22%3A%20%22Headphones%20C%22%2C%0A%20%20%22Orders%22%3A%20%22%3CORDERS%3E%3CORDER%3E%3CQUANTITY%3E222%3C%2FQUANTITY%3E%3CPRICE%3E40.18%3C%2FPRICE%3E%3C%2FORDER%3E...%22%0A%20%7D%2C%0A%20%7B%0A%20%20%22ItemID%22%3A%20%2259100d7b-66a6-4812-9cde-4f95ab56f506%22%2C%0A%20%20%22ItemName%22%3A%20%22Book%20A%22%2C%0A%20%20%22Orders%22%3A%20%22%3CORDERS%3E%3CORDER%3E%3CQUANTITY%3E5577%3C%2FQUANTITY%3E%3CPRICE%3E75.25%3C%2FPRICE%3E%3C%2FORDER%3E...%22%0A%20%7D%2C%0A%20...%0A%5D%0A%3C%2FORDERS%3E%3C%2FORDERS%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20idea%20is%20to%20use%20a%20temporary%20table%20with%20zero%20retention%20in%20conjunction%20with%20-ERR%3AREF-NOT-FOUND-update%20policy%20mechanism%20that%20allows%20massaging%20ingested%20data%20and%20sending%20the%20outcome%20to%20another%20table.%20We%20are%20going%20to%20ingest%20JSON%20into%20the%20temporary%20table%2C%20and%20the%20update%20policy%20will%20extract%20XML%20and%20send%20it%20to%20our%20target%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20important%20caveat%20though%3A%20XML%20chunk%20size%20cannot%20exceed%20128KB.%20If%20there's%20a%20need%20to%20ingest%20a%20bigger%20XML%20chunks%2C%20you%20should%20probably%20be%20looking%20into%20some%20custom%20pre-processing%20method.%20For%20instance%2C%20setting%20up%20an%20Azure%20Function%20that%20transforms%20the%20data%20once%20-ERR%3AREF-NOT-FOUND-it%20arrives%20to%20Blob%20storage%2C%20then%20sends%20the%20transformed%20event%20over%20-ERR%3AREF-NOT-FOUND-Azure%20Event%20Hub%20to%20ADX%2C%20would%20be%20an%20option.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%2C%20our%20target%20table%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3E.create%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EOrders%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3EItemID%3C%2FSPAN%3E%3A%20guid%2C%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EItemName%3C%2FSPAN%3E%3A%20string%2C%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EQuanity%3C%2FSPAN%3E%3A%20real%2C%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EPrice%3C%2FSPAN%3E%3A%20real)%3C%2FPRE%3E%0A%3CP%3EFirst%2C%20let's%20define%20the%20temporary%20table%20with%20zero%20retention.%20Table's%20schema%20reflects%20the%20schema%20of%20JSON%20documents%20that%20we're%20going%20to%20insert%2C%20which%20eliminates%20the%20need%20to%20define%20a%20-ERR%3AREF-NOT-FOUND-data%20mapping.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3E.create%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3ETempTable%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3EItemID%3C%2FSPAN%3E%3A%20guid%2C%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EItemName%3C%2FSPAN%3E%3A%20string%2C%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EOrders%3C%2FSPAN%3E%3A%20string)%0A%0A%3CSPAN%20class%3D%22csl-command%22%3E.alter-merge%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-table%22%3ETempTable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Epolicy%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eretention%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Esoftdelete%3C%2FSPAN%3E%20%3D%200d%20%3CSPAN%20class%3D%22csl-command%22%3Erecoverability%3C%2FSPAN%3E%20%3D%20%3CSPAN%20class%3D%22csl-command%22%3Edisabled%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3ENext%2C%20we%20create%20a%20function%20that%20will%20run%20as%20part%20of%20the%20update%20policy.%20The%20function%20runs%20on%20newly%20arrived%20data%2C%20parses%20XML%20and%20expands%20resulted%20entries%20to%20multiple%20rows%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3E.create-or-alter%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Efunction%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-calculated-column%22%3EExtractOrders%3C%2FSPAN%3E()%0A%7B%0A%20%20%3CSPAN%20class%3D%22csl-table%22%3ETempTable%3C%2FSPAN%3E%0A%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eextend%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOrders%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Eparse_xml%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOrders%3C%2FSPAN%3E)%5B%3CSPAN%20class%3D%22csl-string-literal%22%3E%22Orders%22%3C%2FSPAN%3E%5D%5B%3CSPAN%20class%3D%22csl-string-literal%22%3E%22Order%22%3C%2FSPAN%3E%5D%0A%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Emv-expand%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-query-parameter%22%3Ebagexpansion%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-command%22%3Earray%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EOrders%3C%2FSPAN%3E%0A%20%20%7C%20%3CSPAN%20class%3D%22csl-operator%22%3Eproject%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-column%22%3EItemID%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EItemName%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22csl-column%22%3EQuanity%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Etoreal%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOrders%3C%2FSPAN%3E%5B%3CSPAN%20class%3D%22csl-string-literal%22%3E%22Quantity%22%3C%2FSPAN%3E%5D)%2C%20%3CSPAN%20class%3D%22csl-column%22%3EPrice%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-function%22%3Etoreal%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-column%22%3EOrders%3C%2FSPAN%3E%5B%3CSPAN%20class%3D%22csl-string-literal%22%3E%22Price%22%3C%2FSPAN%3E%5D)%0A%7D%3C%2FPRE%3E%0A%3CP%3EHere%20comes%20the%20most%20magic%20part%3A%20defining%20an%20update%20policy.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3E.alter%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-table%22%3EOrders%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Epolicy%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Eupdate%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%5B%7B'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%20%22IsEnabled%22%3A%20true%2C'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%20%22Source%22%3A%20%22TempTable%22%2C'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%20%22Query%22%3A%20%22ExtractOrders()%22%2C'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%20%22IsTransactional%22%3A%20true%2C'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%20%22PropagateIngestionProperties%22%3A%20false'%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22csl-string-literal%22%3E'%7D%5D'%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EWe're%20ready%20to%20try%20and%20ingest%20some%20data%20now%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20style%3D%22font%3A%2010pt%20consolas%3B%22%3E%3CSPAN%20class%3D%22csl-command%22%3E.ingest%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Einto%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-command%22%3Etable%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22csl-table%22%3ETempTable%3C%2FSPAN%3E%20(%3CSPAN%20class%3D%22csl-string-literal%22%3E%22c%3A%2Ftmp%2Forders.json%22%3C%2FSPAN%3E)%20%3CSPAN%20class%3D%22csl-command%22%3Ewith%3C%2FSPAN%3E(%3CSPAN%20class%3D%22csl-calculated-column%22%3Eformat%3C%2FSPAN%3E%3D%3CSPAN%20class%3D%22csl-string-literal%22%3E%22multijson%22%3C%2FSPAN%3E)%3C%2FPRE%3E%0A%3CP%3ELet's%20query%20the%20Orders%20table%20now%2C%20and%20observe%20the%20results%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20style%3D%22height%3A%20201px%3B%20width%3A%20700px%3B%22%20border%3D%220%22%20width%3D%22700%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22381.333px%22%20height%3D%2229px%22%20class%3D%22csC3260322%22%3E%3CSTRONG%3EItemID%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%22155.556px%22%20height%3D%2229px%22%20class%3D%22csC3260322%22%3E%3CSTRONG%3EItemName%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2293.3333px%22%20height%3D%2229px%22%20class%3D%22csC3260322%22%3E%3CSTRONG%3EQuanity%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2268.4444px%22%20height%3D%2229px%22%20class%3D%22cs1555D232%22%3E%3CSTRONG%3EPrice%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22381.333px%22%20height%3D%2229px%22%20class%3D%22csEED573B2%22%3E59100d7b-66a6-4812-9cde-4f95ab56f506%3C%2FTD%3E%0A%3CTD%20width%3D%22155.556px%22%20height%3D%2229px%22%20class%3D%22csEED573B2%22%3EBook%26nbsp%3BA%3C%2FTD%3E%0A%3CTD%20width%3D%2293.3333px%22%20height%3D%2229px%22%20class%3D%22cs7A7DD7A5%22%3E5577%3C%2FTD%3E%0A%3CTD%20width%3D%2268.4444px%22%20height%3D%2229px%22%20class%3D%22csDDF7EEC5%22%3E75.25%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22381.333px%22%20height%3D%2229px%22%20class%3D%22csEED573B2%22%3E59100d7b-66a6-4812-9cde-4f95ab56f506%3C%2FTD%3E%0A%3CTD%20width%3D%22155.556px%22%20height%3D%2229px%22%20class%3D%22csEED573B2%22%3EBook%26nbsp%3BA%3C%2FTD%3E%0A%3CTD%20width%3D%2293.3333px%22%20height%3D%2229px%22%20class%3D%22cs7A7DD7A5%22%3E50%3C%2FTD%3E%0A%3CTD%20width%3D%2268.4444px%22%20height%3D%2229px%22%20class%3D%22csDDF7EEC5%22%3E12.05%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22381.333px%22%20height%3D%2229px%22%3Ed9a205b6-9423-450e-bafb-cd643623328d%3C%2FTD%3E%0A%3CTD%20width%3D%22155.556px%22%20height%3D%2229px%22%3EHeadphones%26nbsp%3BC%3C%2FTD%3E%0A%3CTD%20width%3D%2293.3333px%22%20height%3D%2229px%22%3E222%3C%2FTD%3E%0A%3CTD%20width%3D%2268.4444px%22%20height%3D%2229px%22%3E40.18%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22381.333px%22%20height%3D%2229px%22%3Ed9a205b6-9423-450e-bafb-cd643623328d%3C%2FTD%3E%0A%3CTD%20width%3D%22155.556px%22%20height%3D%2229px%22%3EHeadphones%26nbsp%3BC%3C%2FTD%3E%0A%3CTD%20width%3D%2293.3333px%22%20height%3D%2229px%22%3E42%3C%2FTD%3E%0A%3CTD%20width%3D%2268.4444px%22%20height%3D%2229px%22%3E45%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%3CBR%20%2F%3EAs%20you%20can%20see%2C%20prices%20and%20quantities%20were%20extracted%20from%20the%20XML%20data%20and%20inserted%20into%20our%20target%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1420562%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20customers%20wonder%2C%20what's%20the%20right%20way%20of%20ingesting%20XML%20data%20into%20Azure%20Data%20Explorer%3F%20Well%2C%20there's%20no%20native%20support%20for%20XML%20format%20yet%2C%20but%20there's%20built-in%20function%20-ERR%3AREF-NOT-FOUND-parse_xml()%26nbsp%3Bthat%20accepts%20XML%20string%20and%20returns%20a%20corresponding%20structure%20of%20dynamic%20type%2C%20which%20we%20could%20probably%20use.%20Thus%2C%20here's%20the%20recipe.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1420562%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eadx%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Many customers wonder, what's the right way of ingesting XML data into Azure Data Explorer? Well, there's no native support for XML format yet, but there's built-in function parse_xml() that accepts XML string and returns a corresponding structure of dynamic type, which we could probably use. Thus, here's the recipe. 

 

To make it even more interesting, let's assume that the data is coming as JSON that includes a string field containing an XML chunk of data (don't ask me why, it's not uncommon to see such interesting blends all over many legacy systems):

 

[
 {
  "ItemID": "d9a205b6-9423-450e-bafb-cd643623328d",
  "ItemName": "Headphones C",
  "Orders": "<Orders><Order><Quantity>222</Quantity><Price>40.18</Price></Order>..."
 },
 {
  "ItemID": "59100d7b-66a6-4812-9cde-4f95ab56f506",
  "ItemName": "Book A",
  "Orders": "<Orders><Order><Quantity>5577</Quantity><Price>75.25</Price></Order>..."
 },
 ...
]

 

The idea is to use a temporary table with zero retention in conjunction with update policy mechanism that allows massaging ingested data and sending the outcome to another table. We are going to ingest JSON into the temporary table, and the update policy will extract XML and send it to our target table.

 

One important caveat though: XML chunk size cannot exceed 128KB. If there's a need to ingest a bigger XML chunks, you should probably be looking into some custom pre-processing method. For instance, setting up an Azure Function that transforms the data once it arrives to Blob storage, then sends the transformed event over Azure Event Hub to ADX, would be an option.

 

Let's say, our target table looks like this:

 

.create table Orders(ItemID: guid, ItemName: string, Quanity: real, Price: real)

First, let's define the temporary table with zero retention. Table's schema reflects the schema of JSON documents that we're going to insert, which eliminates the need to define a data mapping.

 

.create table TempTable(ItemID: guid, ItemName: string, Orders: string)

.alter-merge table TempTable policy retention softdelete = 0d recoverability = disabled

Next, we create a function that will run as part of the update policy. The function runs on newly arrived data, parses XML and expands resulted entries to multiple rows:

 

.create-or-alter function ExtractOrders()
{
  TempTable
  | extend Orders=parse_xml(Orders)["Orders"]["Order"]
  | mv-expand bagexpansion=array Orders
  | project ItemID, ItemName, Quanity=toreal(Orders["Quantity"]), Price=toreal(Orders["Price"])
}

Here comes the most magic part: defining an update policy.

 

.alter table Orders policy update
'[{'
' "IsEnabled": true,'
' "Source": "TempTable",'
' "Query": "ExtractOrders()",'
' "IsTransactional": true,'
' "PropagateIngestionProperties": false'
'}]'

We're ready to try and ingest some data now:

 

.ingest into table TempTable ("c:/tmp/orders.json") with(format="multijson")

Let's query the Orders table now, and observe the results:

 

ItemID ItemName Quanity Price
59100d7b-66a6-4812-9cde-4f95ab56f506 Book A 5577 75.25
59100d7b-66a6-4812-9cde-4f95ab56f506 Book A 50 12.05
d9a205b6-9423-450e-bafb-cd643623328d Headphones C 222 40.18
d9a205b6-9423-450e-bafb-cd643623328d Headphones C 42 45


As you can see, prices and quantities were extracted from the XML data and inserted into our target table.