sql - 将 xml .param SSIS 文件读入 SQL
问题描述
在当前的帖子中,我没有看到与此类似的 xml 文件。我正在尝试使用的文件是 Project.params SSIS 文件,如下所示:
<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
<SSIS:Parameter SSIS:Name="PackageVersion">
<SSIS:Properties>
<SSIS:Property SSIS:Name="ID">{673faa65-53ec-483d-8af2-960987977177}</SSIS:Property>
<SSIS:Property SSIS:Name="CreationName" />
<SSIS:Property SSIS:Name="Description">The package version</SSIS:Property>
<SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
<SSIS:Property SSIS:Name="Required">0</SSIS:Property>
<SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
<SSIS:Property SSIS:Name="Value">1.0.0</SSIS:Property>
<SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
</SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="Debug">
<SSIS:Properties>
<SSIS:Property SSIS:Name="ID">{501b3d7c-f7ac-429b-bb2d-c314649d9c98}</SSIS:Property>
<SSIS:Property SSIS:Name="CreationName" />
<SSIS:Property SSIS:Name="Description">Determines whether the package is ran in debug mode</SSIS:Property>
<SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
<SSIS:Property SSIS:Name="Required">0</SSIS:Property>
<SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
<SSIS:Property SSIS:Name="Value">true</SSIS:Property>
<SSIS:Property SSIS:Name="DataType">3</SSIS:Property>
</SSIS:Properties>
</SSIS:Parameter>
</SSIS:Parameters>
我试图在这里关注帖子:在 SQL Server 中读取 xml 文件 只是没有成功检索数据。这是我到目前为止的代码:
;WITH rs (xmldata) AS
(
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn
FROM OPENROWSET(BULK 'C:\Software\Project.params', SINGLE_BLOB) AS x
),cte AS
(
SELECT a.value('@Parameter', 'NVARCHAR(50)') AS Parameter
, c.value('@ID','NVARCHAR(50)') AS ID
, c.value('@CreationName','NVARCHAR(50)') AS CreationName
, c.value('@Description','NVARCHAR(100)') AS [Description]
, c.value('@IncludeInDebugDump','INT') AS IncludeInDebugDump
, c.value('@Required','INT') AS [Required]
, c.value('@Sensitive','INT') AS Sensitive
, c.value('@Value','NVARCHAR(10)') AS [Value]
, c.value('@DataType','INT') AS DataType
FROM rs AS tbl
CROSS APPLY tbl.xmldata.nodes('/Parameters') AS t1(a)
OUTER APPLY t1.a.nodes('/Parameter') AS t2(b)
OUTER APPLY t2.b.nodes('/Properties') AS t3(c)
OUTER APPLY t3.c.nodes('/Property') AS t4(d)
)
select * from cte
这运行成功(第一个 cte rs 正确提取数据)但第二个 cte 不返回任何数据。没有错误,我得到了结果中的列,只是没有行。任何指导将不胜感激。
解决方案
您需要在查询中指定命名空间,您需要过滤属性值上的节点,这很冗长,但这应该对您有用,尽管您必须添加所需的剩余列。
MS SQL Server 2017 架构设置:
查询 1:
Declare @xmldata as xml =
'<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS">
<SSIS:Parameter SSIS:Name="PackageVersion">
<SSIS:Properties>
<SSIS:Property SSIS:Name="ID">{673faa65-53ec-483d-8af2-960987977177}</SSIS:Property>
<SSIS:Property SSIS:Name="CreationName" />
<SSIS:Property SSIS:Name="Description">The package version</SSIS:Property>
<SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
<SSIS:Property SSIS:Name="Required">0</SSIS:Property>
<SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
<SSIS:Property SSIS:Name="Value">1.0.0</SSIS:Property>
<SSIS:Property SSIS:Name="DataType">18</SSIS:Property>
</SSIS:Properties>
</SSIS:Parameter>
<SSIS:Parameter SSIS:Name="Debug">
<SSIS:Properties>
<SSIS:Property SSIS:Name="ID">{501b3d7c-f7ac-429b-bb2d-c314649d9c98}</SSIS:Property>
<SSIS:Property SSIS:Name="CreationName" />
<SSIS:Property SSIS:Name="Description">Determines whether the package is ran in debug mode</SSIS:Property>
<SSIS:Property SSIS:Name="IncludeInDebugDump">0</SSIS:Property>
<SSIS:Property SSIS:Name="Required">0</SSIS:Property>
<SSIS:Property SSIS:Name="Sensitive">0</SSIS:Property>
<SSIS:Property SSIS:Name="Value">true</SSIS:Property>
<SSIS:Property SSIS:Name="DataType">3</SSIS:Property>
</SSIS:Properties>
</SSIS:Parameter>
</SSIS:Parameters>'
这是查询:
;WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/SSIS' as ns),
RS AS
(
SELECT @xmldata as xmlData
)
SELECT Parm.value('@ns:Name', 'NVARCHAR(50)') AS Parameter,
Id.value('.', 'NVARCHAR(50)') AS Id,
CreationName.value('.', 'NVARCHAR(50)') AS CreationName,
[Description].value('.', 'NVARCHAR(100)') AS [Description]
FROM RS
CROSS APPLY xmlData.nodes('/ns:Parameters/ns:Parameter') as T2(Parm)
OUTER APPLY T2.Parm.nodes('./ns:Properties') AS T3(Props)
OUTER APPLY T3.Props.nodes('./ns:Property[@ns:Name="ID"]') AS T4(Id)
OUTER APPLY T3.Props.nodes('./ns:Property[@ns:Name="CreationName"]') AS T5(CreationName)
OUTER APPLY T3.Props.nodes('./ns:Property[@ns:Name="Description"]') AS T6([Description])
结果:
| Parameter | Id | CreationName | Description |
|----------------|----------------------------------------|--------------|-----------------------------------------------------|
| PackageVersion | {673faa65-53ec-483d-8af2-960987977177} | | The package version |
| Debug | {501b3d7c-f7ac-429b-bb2d-c314649d9c98} | | Determines whether the package is ran in debug mode |
推荐阅读
- bash - 从文本文件中提取属性值
- google-cloud-storage - 我们可以使用单个 Google Cloud Dataflow 将来自多个 Pubsub(源)的数据写入多个 GCS(接收器)吗?
- wordpress - woocommerce 中的四舍五入价格
- c++ - 使用虚拟析构函数c ++删除指向基类的指针(*&)
- node.js - Nodejs - mongodb - 有没有一种简单的方法来获得分组/聚合结果?
- sql - 同时计算会话数
- matlab - 在 Matlab 中绘制 3d 数据
- c# - NAudio Asio 录制播放和保存(仅限噪音)
- python - Chromedriver没有足够的权限,即使我是这台机器上唯一的管理员
- angular - 垫子表单域颜色变化