January 27, 2016 at 10:20 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply