More XML Query Magic Required

Question

More XML Query Magic Required

SSC Guru

Points: 246962

May 24, 2020 at 10:43 pm

Following on from this topic, which Mark Cowne answered perfectly, I have another SSIS XML puzzle, this time with project parameters.

Here is the text version of a very simple Project.params file

2020-05-24_17-30-28

As you can see, there is one project parameter and its value is zero. In SSDT, it looks like this

2020-05-24_17-33-04

I would like to write a query which returns all project parameter names and values. The problem I have encountered here is that there is a series of elements all called 'Property'. I can't work out how to return only the one where Name = "Value"

Here is some setup code:

DROP TABLE IF EXISTS #FileList2;

CREATE TABLE #FileList2
(
    FileName NVARCHAR(260)
   ,FileXML XML
);

INSERT #FileList2
VALUES
(N'Project.params'
,N'<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS"><SSIS:Parameter SSIS:Name="TastyProjectParameter"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{4d9220d2-1f5e-431f-ac32-89515da34357}</SSIS:Property><SSIS:Property SSIS:Name="CreationName" /><SSIS:Property SSIS:Name="Description" /><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">0</SSIS:Property><SSIS:Property SSIS:Name="DataType">9</SSIS:Property></SSIS:Properties></SSIS:Parameter></SSIS:Parameters>');

SELECT PackageName = 'Project'
      ,ItemType = 'Project Parameter'
      ,ItemNamespace = '$Project'
      ,ItemName = p.n1.value('(@*:Name)[1]', 'varchar(30)')
      ,ItemValue = q.n1.value('(@*:Name)[1]', 'varchar(4000)')
FROM #FileList2 fl
    CROSS APPLY fl.FileXML.nodes('//*:Parameters/*:Parameter') p(n1)
    CROSS APPLY p.n1.nodes('*:Properties/*:Property') q(n1);

Which returns this

2020-05-24_17-37-53

... that is, one row for every Property element, together with the name of the property.

What do I need to do to this query, such that

Only row (7) (ie, the 'Value' property) is returned?
Instead of the property name ('Value'), the actual value is returned (zero, in this case)?

Thanks for any assistance.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

To get the "VALUE" of the Property you could use:

ItemValue = q.n1.value('(.)[1]','VARCHAR(4000)')

To get only the case where ItemValue is "Value", you should be able to toss that into a WHERE clause. Something along the lines of:

SELECT PackageName = 'Project'
      ,ItemType = 'Project Parameter'
      ,ItemNamespace = '$Project'
      ,ItemName = p.n1.value('(@*:Name)[1]', 'varchar(30)')
  ,ItemValue = q.n1.value('(.)[1]','VARCHAR(4000)')
FROM #FileList2 fl
    CROSS APPLY fl.FileXML.nodes('//*:Parameters/*:Parameter') p(n1)
    CROSS APPLY p.n1.nodes('*:Properties/*:Property') q(n1)
WHERE q.n1.value('(@*:Name)[1]','VARCHAR(4000)') = 'Value'

Not sure if that is exactly what you are looking for, but seems to be close to the requirements. Also, my XQUERY is flaky at best, so this may not be the most efficient code (I am fairly certain that WHERE clause is going to be slow).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Not sure if that is exactly what you are looking for ...

Yes, that worked for me. It is a little slow, but I'm not dealing with large datasets, so it's acceptable. Thank you very much.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76987 More actions · Answer 3

I'm not sure if this is more efficient, but this also works.

SELECT PackageName = 'Project'
      ,ItemType = 'Project Parameter'
      ,ItemNamespace = '$Project'
      ,ItemName = p.n1.value('(@*:Name)[1]', 'varchar(30)')
      ,ItemValue = q.n1.value('(@*:Name)[1]', 'varchar(4000)')
FROM #FileList2 fl
    CROSS APPLY fl.FileXML.nodes('//*:Parameters/*:Parameter') p(n1)
    CROSS APPLY p.n1.nodes('*:Properties/*:Property[@*:Name="Value"]') q(n1);

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

drew.allen wrote:

I'm not sure if this is more efficient, but this also works.

SELECT PackageName = 'Project'
      ,ItemType = 'Project Parameter'
      ,ItemNamespace = '$Project'
      ,ItemName = p.n1.value('(@*:Name)[1]', 'varchar(30)')
      ,ItemValue = q.n1.value('(@*:Name)[1]', 'varchar(4000)')
FROM #FileList2 fl
    CROSS APPLY fl.FileXML.nodes('//*:Parameters/*:Parameter') p(n1)
    CROSS APPLY p.n1.nodes('*:Properties/*:Property[@*:Name="Value"]') q(n1);

Drew

Thank you.

It does run a little faster, but returns the text 'Value' for every row of data. After modifying the code to return the actual value, the improvement in processing time disappeared. I think I prefer this syntax, however.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin