More XML Query Magic Required

  • Phil Parkin

    SSC Guru

    Points: 244578

    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

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

    Thanks for any assistance.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22904

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

  • Phil Parkin

    SSC Guru

    Points: 244578

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • drew.allen

    SSC Guru

    Points: 76735

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

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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