sql-server - SQL Server:如何使用节点字段导入 XML
问题描述
我正在遵循本指南,该指南教我如何将 XML 文件导入 SQL Server。
我的 XML 如下所示:
<?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's Street"/>
<tag k="addr:country" v="GB"/>
<tag k="addr:postcode" v="CB2 3AR"/>
<tag k="addr:housenumber" v="38-39"/>
</node>
</osm>
所以我首先以这种方式导入了 XML:
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
然后导入id
,lat
并long
从每个<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
完美,它有效!
但现在我被困住了。为了使用 and 创建另外 2 个列,我需要做name
什么website
?
因为<tag>
总是一样的,我需要v=
代表什么是 vale k=
解决方案
您还可以使用 SQL Server 中的本机 XQuery 支持来完成此操作,而不必求助于OPENXML
因内存泄漏和其他问题而闻名的遗留调用。
只需使用此代码:
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)
而且我还建议始终使用最合适的数据类型 - 在这里,id
看起来像INT
- 所以就这样使用它 - 并且lat
显然lon
是DECIMAL
值;不要只是将所有内容都转换为字符串,因为您懒得弄清楚要使用什么!
推荐阅读
- docker - 从 Jenkins Docker 容器旋转 Docker / ECS 容器
- javascript - 在表单“action”之前输入“submit”做onclick
- rest - 用于查询参数的 aws apigateway swagger 文件的集成 uri 是什么?
- matlab - 如何在Matlab中将函数与多维常数矩阵集成?
- node.js - 在 Windows10 上重新安装 NodeJS 和 NPM 时出现问题
- angular - 如何通过选择角度选项来显示输入字段?
- stored-procedures - 在运行时创建执行 SQL 任务
- android - 我已将 JSON 数据加载到对象中,我无法将它们连接到数组适配器以在多个微调器上显示它们
- java - 使用 netbeans 命令行在 Windows 上编译 Autopsy
- python - 从两个字符串返回具有相同长度的交替字母