Retrieve Extended data from KML files.

[jcf]

This is a solution rather than a question. In essence I built a load script that can be imported such that a user can simply pass the kml file location to a subroutine and create a table for extended data.  It does not matter what the key:value pairs are or how many.  The users load script should look like this:

//Include the sub routine file which includes the vShpKey f(x)
$(must_include=[lib://KMLParser.txt])

LOAD
cb_2017_us_county_20m.Name,
cb_2017_us_county_20m.Point,
cb_2017_us_county_20m.Area,
//you need to add this line to create a key back to the extended data
$(vShpKey(cb_2017_us_county_20m.Area)) as ShpKey,
cb_2017_us_county_20m.Line
FROM [lib://TIAA/cb_2017_us_county_20m.kml]
(kml, Table is [cb_2017_us_county_20m/cb_2017_us_county_20m]);

call KMLParse('[lib://cb_2017_us_county_20m.kml]');

I have attached the kmlParser file and the QVF.  let me know if you have any additional questions.  thanks

[LZucco]

Hi @jcf 

Can you help me to retrieve LT_handle ExtendedData from my KML file?

Check attached file.

Thanks in advance + regards

[LZucco]

when changing GEOID to LT_handle inside KMLParser.txt

//create a key to join back to the kml file
Join(ExtndData)
LOAD
right(mid(description,index(description,'LT_Handle'),29),5) as LT_handle
left(replace("Polygon/outerBoundaryIs/LinearRing/coordinates",'0.0 ','')
,len(replace("Polygon/outerBoundaryIs/LinearRing/coordinates",'0.0 ',''))-4) as ShpKey
FROM $(vFileLoc)
(XmlSimple, table is [kml/Document/Folder/Placemark]);

That's the error I get (see attached image)

Unexpected token: 'left', expected one of: ',', 'AutoGenerate', 'From', 'From_Field', 'Inline', 'Resident', 'Extension', ...

[QlikTom]

Works like a charm

Edit:
Works, mostly like a charm. This method has challenges entries with more than one polygon. Any extended data associated with these such as the image below, will not be associated with the original KML load . This is because the KML connector will combine multiple geometries into one field. 

That being said, it seems to only effect .4% of counties in the US, so a 99.6% hit rate, is pretty good.