How can I shred XML report format to a database?

[Big Daddy Leon]

I'm using the .NET Client Library. AdWords API allows downloading report data in a number of formats. I am using the XML format as illustrated in the sample code:

 ReportDefinition definition = new ReportDefinition()

                {

                    reportName = "GENDER_PERFORMANCE_REPORT",

                    reportType = ReportDefinitionReportType.GENDER_PERFORMANCE_REPORT,

                    downloadFormat = DownloadFormat.XML,

                    ....

The XML document that is created is of this format:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<report>

<report-name name="GENDER_PERFORMANCE_REPORT"/>

<date-range date="Feb 24, 2016"/>

-<table>

-<columns>

<column name="currency" display="Currency"/>

<column name="account" display="Account"/>

<column name="timeZone" display="Time zone"/>

....

           100 more columns

....

<column name="viewThroughConv" display="View-through conv."/>

<column name="week" display="Week"/>

<column name="year" display="Year"/>

</columns>

<row year="2016" week="2016-02-22" viewThroughConv="0" views="0" ............ currency="USD"/>

<row year="2016" week="2016-02-22" viewThroughConv="0" views="0" ............ currency="USD"/>

<row year="2016" week="2016-02-22" viewThroughConv="0" views="0" ............ currency="USD"/>

<row year="2016" week="2016-02-22" viewThroughConv="0" views="0" ............ currency="USD"/>

<row year="2016" week="2016-02-22" viewThroughConv="0" views="0" ............ currency="USD"/>

etc.

  </table>

</report>

Why is the data in this format? I can't seem to process this using .NET approaches such as XmlDocument(). I tried a couple of XML parsers that complain that the format above is invalid. I'm thrown off by this format, i.e. inclusion of a node for column names. I'm wondering why the data isn't in the standard format such as this:

<rows>

  <row> 

    <year>2016</year>

    <week>2016-02-22</week>

    ...

  </row>

  <row>

    <year>2016</year>

    <week>2016-02-22</week>

   ...

  </row>

   etc

</rows>

How do you suggest parsing this - I basically want to shred the data to a SQL Server table. The approach I've used in the past doesn't work with your report structure.

Thanks

[Nadine Sundquist (AdWords API Team)]

Greetings!

So, what you're seeing is the use of XML attributes. One of the reasons we use it in our tags rather than expanding them into separate tags is because it significantly decreases the size of the output coming back, which is a performance win for people pulling large reports. In .NET, you can use methods off of XmlDocument such as GetElementsByTagName to retrieve each of those attributes.

Happy coding,

Nadine, AdWords API Team 

[chris h]

Tell us the parse error you are getting with XmlDocument

On Friday, March 4, 2016 at 3:12:43 PM UTC-5, Big Daddy Leon wrote: