Post reply

XML Parsing

  • June 8, 2016 at 2:36 pm

    #319369

    I'm stuck on what I think should be an easy solution. I am trying to read the project.params file that SSIS creates and am having difficulty. I can read the variable name, but how do I get the value? What am I missing?

    declare @x xml = '<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS"><SSIS:Parameter SSIS:Name="FileFolderName"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{aaeb860f-66f6-40b9-aa99-487b880767d8}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></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">C:\TransferIn\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter><SSIS:Parameter SSIS:Name="ArchiveFolder"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{0b640567-dc04-44fe-9a3f-0494135326e2}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></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">C:\Archive\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter></SSIS:Parameters>'

    ;with XMLNamespaces ('www.microsoft.com/SqlServer/SSIS' as ns)

    select ColName = rec.value('@ns:Name', 'varchar(50)')

    -- ,ColValue = rec.value('ns:Properties/ns:Property', 'varchar(50)')

    from @X.nodes('//ns:Parameters/ns:Parameter')as x(rec)

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 8, 2016 at 11:38 pm

    #1884057

    Quick solution

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    declare @x xml = '<SSIS:Parameters xmlns:SSIS="www.microsoft.com/SqlServer/SSIS"><SSIS:Parameter SSIS:Name="FileFolderName"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{aaeb860f-66f6-40b9-aa99-487b880767d8}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></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">C:\TransferIn\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter><SSIS:Parameter SSIS:Name="ArchiveFolder"><SSIS:Properties><SSIS:Property SSIS:Name="ID">{0b640567-dc04-44fe-9a3f-0494135326e2}</SSIS:Property><SSIS:Property SSIS:Name="CreationName"></SSIS:Property><SSIS:Property SSIS:Name="Description"></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">C:\Archive\</SSIS:Property><SSIS:Property SSIS:Name="DataType">18</SSIS:Property></SSIS:Properties></SSIS:Parameter></SSIS:Parameters>'

    ;WITH XMLNamespaces ('www.microsoft.com/SqlServer/SSIS' AS ns)

    SELECT

    ColName = rec.value('@ns:Name', 'varchar(50)')

    ,PROP.VAL.value('local-name(.)','VARCHAR(50)') AS PROP_ELEMENT

    ,PROP.VAL.value('@ns:Name','VARCHAR(100)') AS PROP_NAME

    ,PROP.VAL.value('.[1]','VARCHAR(100)') AS PROP_VALUE

    from @X.nodes('//ns:Parameters/ns:Parameter')as x(rec)

    OUTER APPLY x.rec.nodes('.//*') AS PROP(VAL);

    Output

    ColName PROP_ELEMENT PROP_NAME PROP_VALUE

    ----------------- ------------- --------------------- ------------------------------------------------------------

    FileFolderName Properties NULL {aaeb860f-66f6-40b9-aa99-487b880767d8}000C:\TransferIn\18

    FileFolderName Property ID {aaeb860f-66f6-40b9-aa99-487b880767d8}

    FileFolderName Property CreationName

    FileFolderName Property Description

    FileFolderName Property IncludeInDebugDump 0

    FileFolderName Property Required 0

    FileFolderName Property Sensitive 0

    FileFolderName Property Value C:\TransferInFileFolderName Property DataType 18

    ArchiveFolder Properties NULL {0b640567-dc04-44fe-9a3f-0494135326e2}000C:\Archive\18

    ArchiveFolder Property ID {0b640567-dc04-44fe-9a3f-0494135326e2}

    ArchiveFolder Property CreationName

    ArchiveFolder Property Description

    ArchiveFolder Property IncludeInDebugDump 0

    ArchiveFolder Property Required 0

    ArchiveFolder Property Sensitive 0

    ArchiveFolder Property Value C:\ArchiveArchiveFolder Property DataType 18

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

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