I've done a fair bit of the postcode/coordinate/nearest stuff before but I've never used the OPENROWSET, BULK method to open a file. Brilliant !!

And if that wasn't enough the sample data is also excellent. I've been looking for a list of postcode/coordinate data for a while. Is the stuff here available from somewhere ? updated regularly ?

Sadly not. All done laboriously some years ago, in both cases, and cannot be used commercially but great for this sort of work.

Thanks for the comments on the article. Much appreciated. Phil is rather pleased with the BCP method of saving the XML fragment to file using queryout. Can it be done wothout using a permanent table?

TV star and a Software Geek .. hard to believe..maybe she has a geeky boyfriend RedGate who comes up with these articles to make her look supernatural !! wat say folks ??

Was a TV star, not any more, I'm afraid. Married too. And, did you get your girlfriend to write the comment?

Hi !! did not mean to put you down ..
i have read a few articles of yours and they are very well written.
So are you a full time Techie now ??

On Robyn's behalf, may I just say that she is currently on maternity leave from a job working for a City of London Financial Services company. I've been helping her with her writings, as you'll notice from the head of this article. Now back to XML......

Hi There,

This has been of great help. Just one thing I'm stumbling on. What if I need to import a child of a child element into the same row.

For example, in your locations example. if the xml sample is:

<locations>
<location>
<whereabouts>
<town>Prehen Park</town>
<city>Londonderry</city>
</whereabouts
<county>County Londonderry</county>
<region>Northern Ireland</region>
</location>
</locations>

In the above the <whereabouts> has two child nodes, and I want to import those into the table into the SAME row as the other info. For example, one row of data would be: town, city, county, and region. What would the sql insert into-select statement be when the town and city are child nodes?

Thanks!
kc

kc, you can extend Robyn's sample and simply reference the child elements through a different xpath expression like this (Don't forget to fix the missing close bracket on the whereabouts element in your sample xml ;)) :

SELECT x.location.value( 

         'whereabouts[1]/town[1]', 'varchar(100)') AS whereaboutstown, 

       x.location.value( 

         'whereabouts[1]/city[1]', 'varchar(100)') AS whereaboutscity, 

       x.location.value( 

         'county[1]', 'varchar(80)') AS county, 

       x.location.value( 

         'region[1]', 'varchar(80)') AS region

FROM @xml.nodes('locations/location') AS x(location)

Thanks, Dan. That is a great help.
The coordinates in the locations XML would also be much neater as
  <coordinate>
    <x>3948</x>
    <y>267</y>
  </coordinate>
likewise latitude/longitude.
Robyn and I were wondering whether to do it this way but we decided that we wanted to keep everything plain and simple for this workbench!

Well done Robyn! (we all know that Phil doesn't really contribute :))

In all seriousness, it was a great article. As something I haven't used much, but get asked about occasionally, this was a great starting point.

Did anyone else experience very long processing time to insert the rows from @XMLLocations into the Location table - I let the query run for 20 minutes or more before killing it. Even inserting just 100 records (Select Top 100) is taking many minutes.

This seems odd to me - any ideas?

Yes. Odd. It took a few seconds when we did it. we were surprised how quick it was. Puzzled. Have you tried taking out any indexes?

Thanks Dan for the help with the child elements. That was very helpful!

I too am experiencing the slowness issue. I don't have any indexes defined as far as I'm aware. I took the sample data and query right off the site to test.

I'm using SQL Server 2005. Any ideas

Thanks Dan for the help with the child elements. That was very helpful!

I too am experiencing the slowness issue. I don't have any indexes defined as far as I'm aware. I took the sample data and query right off the site to test.

I'm using SQL Server 2005. Any ideas