Read xml .param SSIS file into SQL

Question

I have not seen an xml file similar to this one with the current postings. The file I am trying to work with is the Project.params SSIS file that looks like this:

<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>

I tried to follow the post here: Read xml file in SQL Server just not having success in getting the data retrieved. Here is the code I have so far:

;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

This runs successfully (the first cte rs pulls the data correctly) but the second cte returns no data. No errors, I get the columns in the Results, just no rows. Any guidance would be greatly appreciated.

Steve Ford · Accepted Answer · 2021-07-08 22:02:27Z

You need to specify the namespaces in your queries, you need to filter the nodes on the attribute value, it is quite long winded but this should work for you, though you'll have to add the remaining columns that you need.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

Query 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>'

Here's the query:

;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])

Results:

|      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 |

Collectives™ on Stack Overflow

Read xml .param SSIS file into SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related