sql-server - 从 XML 节点解析 TSQL
问题描述
该 XML 来自公共资金。SQL Server 似乎认为它不是 XML?我的结果表明 XLT 方法只能涉及列类型 XML。这个来自金库的结果集真的是 XML 吗?
如何将其解析为 SQL 表?
DECLARE @XML NVARCHAR(MAX)
SELECT @XML =
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<feed xml:base="http://data.treasury.gov/Feed.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
<title type="text">DailyTreasuryYieldCurveRateData</title>
<id>http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData</id>
<updated>2020-07-14T05:40:26Z</updated>
<link rel="self" title="DailyTreasuryYieldCurveRateData" href="DailyTreasuryYieldCurveRateData"/>
<entry>
<id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(7633)</id>
<title type="text"/>
<updated>2020-07-14T05:40:26Z</updated>
<author>
<name/>
</author>
<link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(7633)"/>
<category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<content type="application/xml">
<m:properties>
<d:Id m:type="Edm.Int32">7633</d:Id>
<d:NEW_DATE m:type="Edm.DateTime">2020-07-01T00:00:00</d:NEW_DATE>
<d:BC_1MONTH m:type="Edm.Double">0.12</d:BC_1MONTH>
<d:BC_2MONTH m:type="Edm.Double">0.12</d:BC_2MONTH>
<d:BC_3MONTH m:type="Edm.Double">0.14</d:BC_3MONTH>
<d:BC_6MONTH m:type="Edm.Double">0.17</d:BC_6MONTH>
<d:BC_1YEAR m:type="Edm.Double">0.16</d:BC_1YEAR>
<d:BC_2YEAR m:type="Edm.Double">0.17</d:BC_2YEAR>
<d:BC_3YEAR m:type="Edm.Double">0.19</d:BC_3YEAR>
<d:BC_5YEAR m:type="Edm.Double">0.31</d:BC_5YEAR>
<d:BC_7YEAR m:type="Edm.Double">0.52</d:BC_7YEAR>
<d:BC_10YEAR m:type="Edm.Double">0.69</d:BC_10YEAR>
<d:BC_20YEAR m:type="Edm.Double">1.2</d:BC_20YEAR>
<d:BC_30YEAR m:type="Edm.Double">1.43</d:BC_30YEAR>
<d:BC_30YEARDISPLAY m:type="Edm.Double">1.43</d:BC_30YEARDISPLAY>
</m:properties>
</content>
</entry>
</feed>'
SELECT b.value('@d:Id', 'varchar(28)') as d_id
,b.value('@d:BC_30YEAR', 'double') as d_BC_30YEAR
FROM @XML.nodes('feed/entry/content type/m:properties/') as a(b)
解决方案
首先,定义你@XML
的类型XML
:
DECLARE @XML XML;
然后:您需要定义相关的 XML 命名空间!
试试这段代码:
-- define XML namespaces!
;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom',
'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' AS m,
'http://schemas.microsoft.com/ado/2007/08/dataservices' AS d)
SELECT
b.value('(d:Id)[1]', 'varchar(28)') as d_id,
b.value('(d:BC_30YEAR)[1]', 'decimal(20,2)') as d_BC_30YEAR
FROM
@XML.nodes('feed/entry/content/m:properties') as a(b)
这将返回以下值:
我所做的修复:
FROM @XML.nodes('feed/entry/content type/m:properties/') as a(b)
^^^^^^^^^^^^
错误的节点名称 - 节点是<content>
(不是“内容类型”) - 这type
只是节点上的一个属性,在此 XPath 中不相关。
SELECT b.value('@d:Id', 'varchar(28)') as d_id
^^^^^^
您真的想选择 XML元素(节点)d:Id
——而不是属性@
——in@d:Id
表示一个属性!
b.value('@d:BC_30YEAR', 'double') as d_BC_30YEAR
^^^^^^^^^
您需要在decimal(20,2)
这里使用正确的 T-SQL 数据类型——比如——而不是“双重”......
推荐阅读
- git - Jenkins 多分支管道在项目存储库中使用变量
- linux - 错误:Ubuntu 中 Qemu 上的虚拟 Raspbian
- c# - 无法将 x 类型的对象转换为 y 类型
- c++ - 如何从 malloc 编辑的 std::string 创建一个 std::string?
- python - 如何将视频流从一个 Python 传递到另一个?
- javascript - 如何在 vueJS 中滚动查看新添加的列表项
- arrays - 快速钻取子数组
- laravel - 如何在 10 月 CMS 后端菜单中隐藏 Builder 项目?
- r - 找到二元因变量的序数预测变量的相对重要性
- python - 用零替换列表中的负值