如何编写查询，从url中获取xml文件，并在现有数据库中创建sql表

Question

BSD

嗨，你能帮我找到一种写sql查询的方法吗？从url中获取xml文件并从中生成SQL表？

这就是我开始做的：

SET TEXTSIZE -1;
DECLARE @Plant TABLE (ID INT IDENTITY PRIMARY KEY, url NVARCHAR(1000));
INSERT INTO @Plant (url) VALUES
('https://www.w3schools.com/xml/plant_catalog.xml'),   
SELECT @Plant

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @plant
SELECT *
FROM OPENXML (@hdoc, '/CATALOG/PLANT' , 2)
WITH(
CarInfo INT,
Price INT,
Type VARCHAR(100)
)


EXEC sp_xml_removedocument @hdoc

btw:

如果可能的话，在python中(我假设答案是肯定的(，请发送一个例子。

谢谢！

Answer 1

从SQLServer2005开始，在处理XML数据类型时，最好使用基于w3c标准的XQuery语言。保留Microsoft专有的OPENXML及其配套sp_xml_preparedocument和sp_xml_removedocument只是为了与过时的SQL Server 2000向后兼容。它们的使用减少到只有极少数的边缘案例。

以下是如何从https://www.w3schools.com/xml/plant_catalog.xml'通过使用XQuery方法.nodes()和.value()正确定位URL。

SQL

DECLARE @xml XML = 
N'<?xml version="1.0"?>
<CATALOG>
<PLANT>
<COMMON>Bloodroot</COMMON>
<BOTANICAL>Sanguinaria canadensis</BOTANICAL>
<ZONE>4</ZONE>
<LIGHT>Mostly Shady</LIGHT>
<PRICE>$2.44</PRICE>
<AVAILABILITY>031599</AVAILABILITY>
</PLANT>
<PLANT>
<COMMON>Columbine</COMMON>
<BOTANICAL>Aquilegia canadensis</BOTANICAL>
<ZONE>3</ZONE>
<LIGHT>Mostly Shady</LIGHT>
<PRICE>$9.37</PRICE>
<AVAILABILITY>030699</AVAILABILITY>
</PLANT>
</CATALOG>';
SELECT c.value('(COMMON/text())[1]', 'VARCHAR(20)') AS [Common]
, c.value('(BOTANICAL/text())[1]', 'VARCHAR(50)') AS [Botanical]
, c.value('(ZONE/text())[1]', 'INT') AS [Zone]
, c.value('(LIGHT/text())[1]', 'VARCHAR(20)') AS [Light]
, c.value('(PRICE/text())[1]', 'VARCHAR(20)') AS [Price]
, c.value('(AVAILABILITY/text())[1]', 'VARCHAR(20)') AS [Availability]
FROM @xml.nodes('/CATALOG/PLANT') AS t(c);

输出

+-----------+------------------------+------+--------------+-------+--------------+
|  Common   |       Botanical        | Zone |    Light     | Price | Availability |
+-----------+------------------------+------+--------------+-------+--------------+
| Bloodroot | Sanguinaria canadensis |    4 | Mostly Shady | $2.44 |       031599 |
| Columbine | Aquilegia canadensis   |    3 | Mostly Shady | $9.37 |       030699 |
+-----------+------------------------+------+--------------+-------+--------------+