Thanks in advance for any help offered. I am looking to find the right settings in Power Query to import some Google Shopping formatted XML. You can see an example of the XML code here:
<?xml version="1.0"?>
<rss xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0" version="2.0">
<channel>
<title>Site anme</title>
<link><![CDATA[http://www.example.com/]]></link>
<description>Yada</description>
<language>EN</language>
<category>Fashion</category>
<image><url><![CDATA[http://cdn1.linl.com]]></url></image>
<currency>GBP</currency>
<item>
<title><![CDATA[Some product name]]></title>
<link><![CDATA[http://www.somelinke.com]]></link>
<g:image_link><![CDATA[http://cdn1.some.image.com]]></g:image_link>
<g:age_group>adult</g:age_group>
<g:item_group_id><![CDATA[10033]]></g:item_group_id>
<g:id><![CDATA[10033]]></g:id>
<g:gtin><![CDATA[0207140132]]></g:gtin>
<g:size><![CDATA[200]]></g:size>
<g:title><![CDATA[Title here]]></g:title>
<g:availability>in stock</g:availability>
<g:price>20.00 GBP</g:price>
<g:description><![CDATA[blah blah blah]]</g:description>
<g:mpn><![CDATA[61A0010000]]></g:mpn>
<g:brand><![CDATA[Brand]]></g:brand>
<g:condition><![CDATA[new]]></g:condition>
<g:product_type><![CDATA[Health & Beauty > Personal Care]]></g:product_type>
<g:colour><![CDATA[ ]]></g:colour>
<g:shipping>
<g:price>0.00</g:price>
</g:shipping>
<g:gender>unisex</g:gender>
<g:google_product_category><![CDATA[Health & Beauty]]></g:google_product_category>
<g:identifier_exists><![CDATA[TRUE]]></g:identifier_exists>
</item>
<item>
<title><![CDATA[Some product name]]></title>
<link><![CDATA[http://www.somelinke.com]]></link>
<g:image_link><![CDATA[http://cdn1.some.image.com]]></g:image_link>
<g:age_group>adult</g:age_group>
<g:item_group_id><![CDATA[10033]]></g:item_group_id>
<g:id><![CDATA[10033]]></g:id>
<g:gtin><![CDATA[0207140132]]></g:gtin>
<g:size><![CDATA[200]]></g:size>
<g:title><![CDATA[Title here]]></g:title>
<g:availability>in stock</g:availability>
<g:price>20.00 GBP</g:price>
<g:description><![CDATA[blah blah blah]]></g:description>
<g:mpn><![CDATA[61A0010000]]></g:mpn>
<g:brand><![CDATA[Brand]]></g:brand>
<g:condition><![CDATA[new]]></g:condition>
<g:product_type><![CDATA[Health & Beauty > Personal Care]]></g:product_type>
<g:colour><![CDATA[ ]]></g:colour>
<g:shipping>
<g:price>0.00</g:price>
</g:shipping>
<g:gender>unisex</g:gender>
<g:google_product_category><![CDATA[Health & Beauty]]></g:google_product_category>
<g:identifier_exists><![CDATA[TRUE]]></g:identifier_exists>
</item>
<item>
<title><![CDATA[Some product name]]></title>
<link><![CDATA[http://www.somelinke.com]]></link>
<g:image_link><![CDATA[http://cdn1.some.image.com]]></g:image_link>
<g:age_group>adult</g:age_group>
<g:item_group_id><![CDATA[10033]]></g:item_group_id>
<g:id><![CDATA[10033]]></g:id>
<g:gtin><![CDATA[0207140132]]></g:gtin>
<g:size><![CDATA[200]]></g:size>
<g:title><![CDATA[Title here]]></g:title>
<g:availability>in stock</g:availability>
<g:price>20.00 GBP</g:price>
<g:description><![CDATA[blah blah blah]]></g:description>
<g:mpn><![CDATA[61A0010000]]></g:mpn>
<g:brand><![CDATA[Brand]]></g:brand>
<g:condition><![CDATA[new]]></g:condition>
<g:product_type><![CDATA[Health & Beauty > Personal Care]]></g:product_type>
<g:colour><![CDATA[ ]]></g:colour>
<g:shipping>
<g:price>0.00</g:price>
</g:shipping>
<g:gender>unisex</g:gender>
<g:google_product_category><![CDATA[Health & Beauty]]></g:google_product_category>
<g:identifier_exists><![CDATA[TRUE]]></g:identifier_exists>
</item>
<item>
<title><![CDATA[Some product name]]></title>
<link><![CDATA[http://www.somelinke.com]]></link>
<g:image_link><![CDATA[http://cdn1.some.image.com]]></g:image_link>
<g:age_group>adult</g:age_group>
<g:item_group_id><![CDATA[10033]]></g:item_group_id>
<g:id><![CDATA[10033]]></g:id>
<g:gtin><![CDATA[0207140132]]></g:gtin>
<g:size><![CDATA[200]]></g:size>
<g:title><![CDATA[Title here]]></g:title>
<g:availability>in stock</g:availability>
<g:price>20.00 GBP</g:price>
<g:description><![CDATA[blah blah blah]]></g:description>
<g:mpn><![CDATA[61A0010000]]></g:mpn>
<g:brand><![CDATA[Brand]]></g:brand>
<g:condition><![CDATA[new]]></g:condition>
<g:product_type><![CDATA[Health & Beauty > Personal Care]]></g:product_type>
<g:colour><![CDATA[ ]]></g:colour>
<g:shipping>
<g:price>0.00</g:price>
</g:shipping>
<g:gender>unisex</g:gender>
<g:google_product_category><![CDATA[Health & Beauty]]></g:google_product_category>
<g:identifier_exists><![CDATA[TRUE]]></g:identifier_exists>
</item>
</channel>
</rss>
Try as I might, I cannot find a decent way to get this to import. Power Query is fixated with the opening section. If I remove it entirely then Power Query will ignore any of the
Matt.
I tried your example and it has successfully imported after I closed tag on line 23.
<g:description><![CDATA[blah blah blah]]></g:description> was missing > here:
[CDATA[blah blah blah]]><
let
Source = Xml.Tables(File.Contents("C:\Users\User\Desktop\test.xml")),
ExpandTable = Table.ExpandTableColumn(Source, "channel", {"title", "link", "description", "language", "category", "image", "currency", "item"}, {"channel.title", "channel.link", "channel.description", "channel.language", "channel.category", "channel.image", "channel.currency", "channel.item"})
in
ExpandTable
Embedded tables also seem OK and can be opened as well.