Get from XML all ObjectNames LIKE 'MF%' OR 'MC%'

• info 58414

  Hall of Fame

  Points: 3386

  More actions

  February 13, 2017 at 6:44 am

  #335278

  Hi,
  how can i get all Values from a XML-File (with tsql)
  WHERE ObjectNames LIKE 'MF%' OR 'MC%'    ?
  (The Source-XML is a SSIS-Dtsx-Package).
  Regards
  Nicole

  <DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2">
          <DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property>
          <DTS:Property DTS:Name="ExecutionAddress"></DTS:Property>
          <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
         .....
          <DTS:Property DTS:Name="ObjectName">MFK_LOG_ERRORS</DTS:Property>

  MyCode
  
  SELECT Props.Prop.query('.')                   as PropXml
  ,   Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
            string(./@p1:Name)','nvarchar(max)')       as PropName
  ,   Props.Prop.value('.', 'nvarchar(max)')              as PropValue
  FROM  (
     SELECT  CAST(pkgblob.BulkColumn AS XML) pkgXML
     FROM  OPENROWSET(bulk 'C:\Program Files\Microsoft SQL Server\110\DTS\Packages\Work\SWH_EXT_FKR.dtsx',single_blob) AS pkgblob
     ) t
  CROSS  APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
          /DTS:Executable/DTS:Property') Props(Prop)

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  February 13, 2017 at 7:24 am

  #1928101

  Something like this maybe?
  😎

  SELECT
  PROP.DATA.value('(@Name)[1]','VARCHAR(100)')
FROM @DTSXML.nodes('//Property') AS PROP(DATA)

  You will have to add either the namespace declaration or a namespace wildcard to make it work with the actual data.