problem extract SSIS Package Data, new to XML :-(

• hoylegj

  SSC Veteran

  Points: 286

  More actions

  January 27, 2016 at 10:20 am

  #99052

  I'm trying to list a few bits from the sample XML following this.

  Sample XML

  <DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">

  <DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>

  <DTS:Property DTS:Name="VersionComments" />

  <DTS:Property DTS:Name="CreatorName">barry</DTS:Property>

  .......

  <DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property>

  <DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

  <DTS:ConnectionManager>

  <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>

  <DTS:Property DTS:Name="ObjectName">ExportFile1</DTS:Property>

  <DTS:Property DTS:Name="DTSID">{4E7C-B1D567C2A31C}</DTS:Property>

  <DTS:Property DTS:Name="Description" />

  <DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property>

  <DTS:PropertyExpression DTS:Name="ConnectionString">@[User::ExportPath] +.....

  Using T-SQL, I can read a bunch of stuff but struggling to get the

  <DTS:Property DTS:Name="ObjectName">ExportFile1</DTS:Property>

  ,

  <DTS:PropertyExpression DTS:Name="ConnectionString">@[...............

  ,

  <DTS:Property DTS:Name="CreationName">FLATFILE</

  Sample SQL :

  SELECT '15'

  , x.vals.value('@DTS:CreationName', 'Varchar(255)') AS CreationName

  , x.vals.value('@DTS:Name', 'Varchar(255)') AS CreationName1

  , x.vals.value('@DTS:ObjectName', 'Varchar(255)') AS ObjectName

  , y.vals.value('@DTS:ConnectionString', 'Varchar(255)') AS ConnectionString

  , y.vals.query('.') AS NodesAsExtracted

  FROM @pkgStatsBase p

  CROSS APPLY p.PackageXML.nodes('//DTS:Executable/DTS:ConnectionManager') AS y(vals)

  CROSS APPLY p.PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager/DTS:Property') AS x(vals)

  This retrieves the name list from the /DTS:Property block, but I can't seem to read the actual line data.

  Sample Output :

  CreationName CreationName1 ObjectName ConnectionString NodesAsExtracted

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

  15 NULL DelayValidation NULL NULL <DTS:ConnectionManager xmlns......

  15 NULL ObjectName NULL NULL <DTS:ConnectionManager xmlns......

  15 NULL DTSID NULL NULL <DTS:ConnectionManager xmlns......

  Can anyone explain what I need to do to list the contents of say

  <DTS:Property DTS:Name="ObjectName">ExportFile1<

  ,

  <DTS:PropertyExpression DTS:Name="ConnectionString">@[User::ExportP.....

  ,

  <DTS:Property DTS:Name=" contenest">FLATFILE<

  Sorry its not a working example but the data and code are massive.

  Many thanks