goldfinger
New Member
A beginners question, hopefully easily answered. I've got an xml file I want to load into SQLServer 2008 and extract the useful informaiton.I'm starting simple and just trying to extract the name (\gpx\name). The code I have is
ECLARE @x xml;SELECT @x = xCol.BulkColumnFROM OPENROWSET (BULK 'C:\Data\EM.gpx', SINGLE_BLOB) AS xCol;-- confirm the xml data is in @xselect @x as XML_Data-- try and get the name of the gpx sectionSELECT c.value('name[1]', 'varchar(200)') as Namefrom @x.nodes('gpx') x(c)Below is a heavily shortened version of the xml file:\[code\]<?xml version="1.0" encoding="utf-8"?><gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.0" creator="Groundspeak Pocket Query" xsi:schemaLocation="http://www.topografix.com/GPX/1/0 http://www.topografix.com/GPX/1/0/gpx.xsd http://www.groundspeak.com/cache/1/0 http://www.groundspeak.com/cache/1/0/cache.xsd" xmlns="http://www.topografix.com/GPX/1/0"> <name>EM</name> <desc>Geocache file generated by Groundspeak</desc> <author>Groundspeak</author> <email>[email protected]</email> <time>2010-03-24T14:01:36.4931342Z</time> <keywords>cache, geocache, groundspeak</keywords> <wpt lat="51.2586" lon="-2.213067"> <time>2008-03-30T07:00:00Z</time> <name>GC1APHM</name> <desc>Sandman's Noble Hoard by Sandman1973, Unknown Cache (2/3)</desc> <groundspeak:cache id="832000" available="True" archived="False" xmlns:groundspeak="http://www.groundspeak.com/cache/1/0"> <groundspeak:name>Sandman's Noble Hoard</groundspeak:name> <groundspeaklaced_by>Sandman1973</groundspeaklaced_by> </groundspeak:cache> </wpt></gpx>\[/code\]If the first two lines are replaced with just:\[code\]<gpx>\[/code\]the above example works correctly, however I then can't access groundspeak:name (/gpx/wpt/groundspeak:cache/groundspeak:name), so my guess its a problem with the namespace. Any help would be appriciated.
ECLARE @x xml;SELECT @x = xCol.BulkColumnFROM OPENROWSET (BULK 'C:\Data\EM.gpx', SINGLE_BLOB) AS xCol;-- confirm the xml data is in @xselect @x as XML_Data-- try and get the name of the gpx sectionSELECT c.value('name[1]', 'varchar(200)') as Namefrom @x.nodes('gpx') x(c)Below is a heavily shortened version of the xml file:\[code\]<?xml version="1.0" encoding="utf-8"?><gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.0" creator="Groundspeak Pocket Query" xsi:schemaLocation="http://www.topografix.com/GPX/1/0 http://www.topografix.com/GPX/1/0/gpx.xsd http://www.groundspeak.com/cache/1/0 http://www.groundspeak.com/cache/1/0/cache.xsd" xmlns="http://www.topografix.com/GPX/1/0"> <name>EM</name> <desc>Geocache file generated by Groundspeak</desc> <author>Groundspeak</author> <email>[email protected]</email> <time>2010-03-24T14:01:36.4931342Z</time> <keywords>cache, geocache, groundspeak</keywords> <wpt lat="51.2586" lon="-2.213067"> <time>2008-03-30T07:00:00Z</time> <name>GC1APHM</name> <desc>Sandman's Noble Hoard by Sandman1973, Unknown Cache (2/3)</desc> <groundspeak:cache id="832000" available="True" archived="False" xmlns:groundspeak="http://www.groundspeak.com/cache/1/0"> <groundspeak:name>Sandman's Noble Hoard</groundspeak:name> <groundspeaklaced_by>Sandman1973</groundspeaklaced_by> </groundspeak:cache> </wpt></gpx>\[/code\]If the first two lines are replaced with just:\[code\]<gpx>\[/code\]the above example works correctly, however I then can't access groundspeak:name (/gpx/wpt/groundspeak:cache/groundspeak:name), so my guess its a problem with the namespace. Any help would be appriciated.