USQL - How to extract the attribute value from xml file using xml extractor

Question

How to extract the attribute value from XML file using custom extractor using U-SQL job. I can able to extract the sub element values from XML file.

sample Xml File:
<?xml version="1.0" encoding="UTF-8"?>
<Users>
<User ID="001">
    <FirstName>david</FirstName>
    <LastName>bacham</LastName>
</User>
<User ID="002">
  <FirstName>xyz</FirstName>
  <LastName>abc</LastName>
</User>
</Users>

I can able to extract Firstname and lastname using the below code.How can i get ID value as a part of csv file.

Sample U sql Job:

REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];
@input = EXTRACT 
  FirstName string,
  LastName string 
  FROM @"/USERS.xml"
  USING new Microsoft.Analytics.Samples.Formats.Xml.XmlExtractor("User",
    new SQL.MAP<string, string> { 
    {"FirstName","FirstName"},
    {"LastName","LastName"}
 );

 @output = SELECT * FROM @input;

 OUTPUT @output
 TO "/USERS.csv"
 USING Outputters.Csv();

I think it better if you can update XML and pass Id value as a property like FirstName or LastName. — Ca Pham Van
– Ca Pham Van, Commented Jun 20, 2019 at 16:16

wBob · Accepted Answer · 2019-06-21 00:08:51Z

You can do this easily in Databricks, eg

%sql
CREATE TABLE User
USING com.databricks.spark.xml
OPTIONS (path "/FileStore/tables/input42.xml", rowTag "User")

Then read the table:

%sql
SELECT *
FROM User;

If you must do it with U-SQL then using the XmlDomExtractor from the Formats assembly worked for me:

REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

DECLARE @inputFile string = "/input/input40.xml";

@input =
    EXTRACT 
        id string,
        firstName string,
        lastName string
    FROM @inputFile
    USING new Microsoft.Analytics.Samples.Formats.Xml.XmlDomExtractor(rowPath : "/Users/User",
          columnPaths : new SQL.MAP<string, string>{
          { "@ID", "id" },
          { "FirstName", "firstName" },
          { "LastName", "lastName" }
          }
          );


@output =
    SELECT *
    FROM @input;


OUTPUT @output
TO "/output/output.csv"
USING Outputters.Csv();

My results:

Collectives™ on Stack Overflow

USQL - How to extract the attribute value from xml file using xml extractor

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related