none
Help extract information from XML file

    Question

  • Hi,

    I am quite new to data lake analytics and I am trying to process an XML file with the following structure

      <panel name="Top/Top_120400_1_2" panelID="" omit="false">
       <id ref="pu1">
        <RefDes name="D20" />
        <RefDes name="D18" />
        <RefDes name="D60" />
        <RefDes name="D64" />
       </id>
       <id ref="pu2">
        <RefDes name="Q38" />
        <RefDes name="Q39" />
        <RefDes name="Q2" />
        <RefDes name="Q57" />
        <RefDes name="Q26" />
        <RefDes name="Q32" />
        <RefDes name="Q4" />
        <RefDes name="Q16" />
       </id>
       <id ref="pu3">
        <RefDes name="D96" />
        <RefDes name="D6" />
        <RefDes name="D58" />
        <RefDes name="D117" />
        <RefDes name="D115" />
        <RefDes name="D57" />
        <RefDes name="D52" />
        <RefDes name="D14" />
        <RefDes name="D4" />
        <RefDes name="D127" />
        <RefDes name="D126" />
        <RefDes name="D100" />
        <RefDes name="D101" />
        <RefDes name="D138" />
        <RefDes name="D139" />
        <RefDes name="D51" />
        <RefDes name="D33" />
        <RefDes name="D5" />
        <RefDes name="D134" />
        <RefDes name="D131" />
        <RefDes name="D55" />
        <RefDes name="D56" />
        <RefDes name="D135" />
        <RefDes name="D137" />
        <RefDes name="D123" />
        <RefDes name="D124" />
       </id>

    I need to transform this into a flat file with this format

    Panel name, id ref, RefDes name

    Top/Top_120400_1_2, PU1,D20

    Top/Top_120400_1_2, PU2,Q38

    I have tried using the XML extractor but am bit challenge on how to use it.

    I can extract the indivual levels using for example

    Microsoft

    .Analytics.Samples.Formats.Xml.XPath.Evaluate(@elxml.xmlstring, "TraceabilityData/panels/panel/@name") ASdutArray

    But I am unable to combine it to the format above.

    Any help would be appreciate

    Thanks

    Søren

    Wednesday, May 31, 2017 8:13 AM