Data Feed XML Syntax
One approach for moving XML data into PowerPivot workbooks is to build a data service or Web application that streams XML data in a format and structure that is easily consumed in PowerPivot for Excel. PowerPivot for Excel contains a built-in data feed reader that can parse XML structures that conform to the Atom 1.0 data syndication format described in RFC4287.
Although you can use a variety of tools and applications to generate a data feed, programmers who build a data service that is based on the ADO.NET Data Services 3.5.1 Atom format will create feeds that are fully supported in PowerPivot for Excel. The following products and technologies support Atom feed generation based on the ADO.NET Atom format:
ADO.NET Data Services
Windows Communication Foundation (WCF)
SharePoint 2010 Atom feeds
SQL Server 2008 R2 Reporting Services Atom feeds
Programmers who use other tools or approaches will need to know how to structure a valid XML response that can be processed by the data feed reader that is built into the PowerPivot for Excel. This topic describes the parts of the Atom 1.0 syntax that are supported for PowerPivot workbooks.
For more information about how to use ADO.NET Data Services to generate data feeds from other data sources, see Using ADO.NET Data Services and the Atom Format on the Microsoft web site.
Picture the Data Payload
Before diving into the details of XML syntax, it helps to visualize how the data presents itself in the PowerPivot workspace after an import operation. A PowerPivot data source consists of tabular data that is arranged into rows and columns in a familiar grid layout. When you import structured Atom 1.0 data, the result set includes built-in data elements that are present in any Atom feed, and custom data that contains business or industry data that analysts want to use. Consider the following example, which shows built-in Atom data elements in bold, and custom data elements used to present data from an internal human resources database in italic:
id |
title |
updated |
headcount |
Open positions |
Avg. Years in Dept |
http://AW:8080/AWDept/AWDataService.svc/Dept(1) |
Marketing department |
2008-04-18T18:16:30Z |
15 |
2 |
1.5 |
http://AW:8080/AWDept/AWDataService.svc/Dept(2) |
Production department |
2008-04-18T18:16:30Z |
26 |
3 |
4 |
The above example can be used to illustrate the following key points:
Built-in fields might be part of your result set. Fields such as id, title, and updated are specified in the Atom 1.0 syntax and are a required part of the feed. However, you can use a data feed connection parameter named "Include Atom Elements" to include or exclude the fields. Depending on how you configure the parameter, users can expect to see these fields in the PowerPivot workspace after the feed is imported. After importing a feed, users can delete the Atom fields if they are not meaningful within the context of the data source.
Custom content fields can be used to return any data that is useful in a PowerPivot workbook. In most cases, the majority of the data in the payload consists of custom content.
XML Structure of the Example Payload
An Atom 1.0 data feed includes XML tabular data that includes a combination of built-in elements and custom content elements. The built-in elements provide essential metadata used for processing. Custom data includes the business data you want to make available in the feed. Each row in a tabular dataset includes both built-in and custom elements.
The following example illustrates a simple Atom 1.0 data document that provides the two rows of data in the previous example. Built-in Atom elements are indicated in Bold typeface.
<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed xmlns="http://www.w3.org/2005/Atom">
<title type="text">Departments</title>
<id>http://Adventure-works:8080/AW/AWDataService.svc/Department</id>
<updated>2008-04-18T18:16:30Z</updated>
<entry>
<id>http://AW:8080/AWDept/AWDataService.svc/Dept(1)</id>
<title type="text">Marketing department</title>
<updated>2008-04-18T18:16:30Z</updated>
<content type="application/xml">
<d:headcount type="int">15</d:headcount>
<d:openpositions type="int">2</d:openpositions>
<d:yrsinposition type="int">1.5</d:yrsinposition>
</content>
</entry>
<entry>
<id>http://AW:8080/AWDept/AWDataService.svc/Dept(2)</id>
<title type="text">Production department</title>
<updated>2008-04-18T18:16:30Z</updated>
<content type="application/xml">
<d:headcount type="int">26</d:headcount>
<d:openpositions type="int">3</d:openpositions>
<d:yrsinposition type="int">4</d:yrsinposition>
</content>
</entry>
</feed>
XML Reference
The data or Web service must return a well-formed XML document that contains <feed>, <entry>, and <content> data structures. These elements map to the table structure, row structure, and data values that appear in a table in the PowerPivot workspace after a data feed is imported.
Atom standards do not include provisions for passing schema information to a client that consumes the XML payload. However, a practical approach to ensuring that data definitions are provided to a client application is to specify data attributes as XML attributes. Schema information about data type, format, and other column attributes are generated based on descriptions that are embedded in the XML data elements. Attributes on each element determine whether a column contains numeric or text data.
Feed Element
The Feed element is a container for metadata and data. It describes the table that will be created in the PowerPivot workbook. The following example illustrates the syntax of the feed element as used by PowerPivot for Excel.
<feed xmlns="http://www.w3.org/2005/Atom">
<title type="text">My Table Name</title>
<id>http://UniversalResourceIdentifier/identifiesthisfeed</id>
<updated>2008-10-31:09:23:34:1832</updated>
<entry />
</feed>
Syntax
Element or attribute |
Required |
Description |
|---|---|---|
<feed xmlns= > |
No |
The xmlns attribute is used to resolve element name conflicts. For example, if you have two <id> elements, you can prefix a namespace that identifies each one, where <atom:id> refers to the built-in column and <adventureworks:id> refers to an identifier in an Adventure Works dataset. If you need to disambiguate the built-in Atom fields from other fields that share the same name, you can add the Atom namespace to the feed element (xmlns="http://www.w3.org/2005/Atom"), and then use the atom prefix to identify the element (for example, atom:id, atom:title, atom:updated). |
<title> |
Yes |
The title element contains a text string. As used in a PowerPivot data feed, it specifies the name of the table that is created in the PowerPivot workspace when the data feed is imported into PowerPivot for Excel. At run time, the value for the <title> element is read from the data service document on the SharePoint site. |
<id> |
Yes |
The id element uniquely and permanently identifies an instance of the feed. This value is metadata that is for internal use only. It does not appear in a table in the PowerPivot workspace or in application pages on a SharePoint site. In a PowerPivot data feed, this value is used when comparing future and past instances of the same feed for data refresh purposes. This value must be a URI or IRI. The value is case-sensitive. |
<updated> |
Yes |
The updated element specifies a date or time at which the data feed was modified. This value is metadata that is for internal use only. It does not appear in a table in the PowerPivot workspace or in application pages on a SharePoint site. In a PowerPivot data feed, this value is used when comparing future and past instances of the same feed for data refresh purposes. This value must be a valid date timestamp. |
Ignored Child Elements of Feed
All other elements that are defined for the <entry> element in the Atom 1.0 syndication format are ignored by PowerPivot for Excel. This includes the following:
<Category>
<Contributor>
<Generator>
<Icon>
<Link>
<Logo>
<Rights>
<Subtitle>
<extensionElement>
Entry Element
The <entry> element contains a row of data. The following example illustrates the syntax of the <entry> element as used by PowerPivot for Excel.
<entry>
<id>http://AW:8080/AW/AWDataService.svc/Department(1)</id>
<title type="text" />
<updated>2008-04-18T18:16:30Z</updated>
<content /> ;* contains data
</entry>
Syntax
Element or attribute |
Required |
Description |
|---|---|---|
<id> |
Yes |
The id element uniquely and permanently identifies an instance of the row. This value is used when comparing other instances of the same row, typically during data refresh. This value appears in a row in a PowerPivot table. This value must be a URI or IRI. The value is case-sensitive. |
<title> |
Yes |
The title element specifies a name of the row that is created in a PowerPivot table when the data feed is imported into PowerPivot for Excel. The element must be specified, but it does not require a value. For example, the following element is valid Atom 1.0 syntax: <title type="text" /> |
<updated> |
Yes |
The updated element specifies a date or time at which the data feed was modified. This value appears in a table in the PowerPivot workspace or in application pages on a SharePoint site. |
<content> |
Yes |
This element is not required in Atom 1.0 syntax but it is required to pass in data to a table in the PowerPivot workspace. It contains the custom data you want to use. There is no schema that defines data structures. The schema is generated dynamically during an import operation based on attributes and elements. Within each <content> element is information that is used to derive column names and data type. |
Derived Schema for Custom Content
Data feeds contain autonomous data that is processed as a complete document. As such, there is no separate lookup for schemas. Instead, you must embed data definitions within the XML structures themselves.
Consider the following XML fragment from the previous sample. Notice that data type is specified as an attribute.
<content type="application/xml">
<d:headcount type="int">15</d:headcount>
<d:openpositions type="int">2</d:openpositions>
<d:yrsinposition type="int">1.5</d:yrsinposition>
</content>
Ignored Child Elements of Entry
All other elements that are defined for the <entry> element in the Atom 1.0 syndication format are ignored by the PowerPivot for Excel. This includes the following:
<Category>
<Contributor>
<Link>
<Published>
<Rights>
<Source>
<Summary>
<extensionElement>