SQL Server: how import XML with node's fields

Question

I'm following this guide that is teaching me how to import an XML file to SQL Server.

My XML looks like this:

<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="Osmosis 0.46">
  <bounds minlon="-180.00000" minlat="-90.00000" maxlon="180.00000" maxlat="90.00000" origin="http://www.openstreetmap.org/api/0.6"/>
  <node id="1014954" version="5" timestamp="2016-01-09T23:33:09Z" uid="1894634" user="Wikilux" changeset="36472980" lat="46.4928487" lon="7.5628558">
    <tag k="url" v="www.cine-rex.ch"/>
    <tag k="name" v="Ciné Rex"/>
    <tag k="amenity" v="cinema"/>
    <tag k="website" v="http://www.cine-rex.ch/kino.shtml"/>
    <tag k="addr:street" v="Landstrasse"/>
    <tag k="addr:housenumber" v="18"/>
  </node>
  <node id="20823872" version="7" timestamp="2017-09-12T15:19:00Z" uid="364" user="Edward" changeset="51976823" lat="52.2062941" lon="0.1346864">
    <tag k="name" v="Vue Cambridge"/>
    <tag k="screen" v="8"/>
    <tag k="amenity" v="cinema"/>
    <tag k="operator" v="Vue Cinemas"/>
    <tag k="wikidata" v="Q39197413"/>
    <tag k="cinema:3D" v="yes"/>
    <tag k="addr:street" v="The Grafton Centre"/>
    <tag k="addr:postcode" v="CB1 1PS"/>
  </node>
  <node id="20922159" version="12" timestamp="2017-09-12T15:19:00Z" uid="364" user="Edward" changeset="51976823" lat="52.2028721" lon="0.1234498">
    <tag k="name" v="Arts Picturehouse"/>
    <tag k="screen" v="3"/>
    <tag k="amenity" v="cinema"/>
    <tag k="operator" v="City Screen Limited"/>
    <tag k="wikidata" v="Q39197264"/>
    <tag k="addr:city" v="Cambridge"/>
    <tag k="wheelchair" v="no"/>
    <tag k="addr:street" v="St Andrew&apos;s Street"/>
    <tag k="addr:country" v="GB"/>
    <tag k="addr:postcode" v="CB2 3AR"/>
    <tag k="addr:housenumber" v="38-39"/>
  </node>
</osm>

so I first imported the XML this way:

CREATE DATABASE OPENXMLTesting
GO
USE OPENXMLTesting
GO
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\Users\franc\Desktop\cinema.osm', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML

And then imported id, lat and long from each <node>:

USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT id, lat, lon
FROM OPENXML(@hDoc, 'osm/node')
WITH 
(
id [varchar](50) '@id',
lat [varchar](100) '@lat',
lon [varchar](100) '@lon'
)

EXEC sp_xml_removedocument @hDoc
GO

Perfect, it works!

But now I'm stuck. What do I need to do in order to create 2 more columns with name and website?

Because the <tag> is always the same and I need to take the v= in behalf of what is the vale of k=

Answer 1

You can also do it with native XQuery support in SQL Server, without having to resort to the legacy OPENXML calls which are known for memory leaks and other problem.

Just use this code instead:

DECLARE @XML AS XML

SELECT @XML = XMLData FROM XMLwithOpenXML

SELECT
    id = xc.value('@id', 'int'),
    lon = xc.value('@lon', 'decimal(20,8)'),
    lat = xc.value('@lat', 'decimal(20,8)'),
    name = xc.value('(tag[@k="name"]/@v)[1]', 'varchar(50)'),
    website = xc.value('(tag[@k="website"]/@v)[1]', 'varchar(50)')
FROM
    @XML.nodes('/osm/node') AS XT(XC)

And I also recommend to always use the most appropriate datatype - here, the id looks like an INT - so use it as such - and lat and lon are clearly DECIMAL values ; don't just convert everything to strings because you're too lazy to figure out what to use!

Answer 2

Dammit, I found the solution by myself thanks to this post on StackOverflow:

USE OPENXMLTesting
GO
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT id, lat, lon,name,website
FROM OPENXML(@hDoc, 'osm/node')
WITH 
(
id [varchar](50) '@id',
lat [varchar](100) '@lat',
lon [varchar](100) '@lon',
name [varchar](100) '(tag[@k="name"]/@v)[1]',
website [varchar](500) '(tag[@k="website"]/@v)[1]'
)
EXEC sp_xml_removedocument @hDoc
GO