I'm in the process of retreiving some data from a xml file, it is actually a SSIS package file (dtsx). I've read about using xmllint
, however I'm having a hard time retrieving what I want. I'd like to ask some helps from folks here. This is what my xml looks like:
<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:refId="Package"
DTS:CreationDate="7/22/2019 4:18:27 PM"
DTS:CreationName="Microsoft.Package"
DTS:CreatorComputerName="ADMIN-8DF005D47"
DTS:CreatorName="ADMIN-8DF005D47\Administrator"
DTS:DTSID="{8CCA1D42-642A-4932-AAEC-E02175A4B2DB}"
DTS:ExecutableType="Microsoft.Package"
DTS:LastModifiedProductVersion="15.0.2000.68"
DTS:LocaleID="1033"
DTS:ObjectName="HardestNestedWorkflow2"
DTS:PackageType="5"
DTS:VersionBuild="12"
DTS:VersionGUID="{FDD9B190-1A03-4A19-8794-FA86F4F46A93}">
<DTS:Property
DTS:Name="PackageFormatVersion">8</DTS:Property>
<DTS:ConnectionManagers>
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[ADMIN-8DF005D47.AdventureWorks]"
DTS:CreationName="OLEDB"
DTS:DTSID="{A32A68DF-3D53-4057-AF80-1B8D524F82BC}"
DTS:ObjectName="ADMIN-8DF005D47.AdventureWorks">
<DTS:ObjectData>
<DTS:ConnectionManager
DTS:ConnectionString="Data Source=ADMIN-8DF005D47;Initial Catalog=AdventureWorks;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SSIS-HardestNestedWorkflow2-{A32A68DF-3D53-4057-AF80-1B8D524F82BC}ADMIN-8DF005D47.AdventureWorks;Auto Translate=False;" />
</DTS:ObjectData>
<!--snipped-->
</DTS:ConnectionManager>
</DTS:ConnectionManagers>
</DTS:Executable>
I'm looking to get the value "8" out of this line
<DTS:Property
DTS:Name="PackageFormatVersion">8</DTS:Property>
so, what I did was to issue a command
xmllint --xpath "//*[local-name()='Executable']/*[local-ame()='Property']/text()" HardestNestedWorkflow2_latest.xml
.. and it was good, I was able to extract the value "8"
Now, I wanted to extract the value
Data Source=ADMIN-8DF005D47;Initial Catalog=AdventureWorks;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SSIS-HardestNestedWorkflow2-{A32A68DF-3D53-4057-AF80-1B8D524F82BC}ADMIN-8DF005D47.AdventureWorks;Auto Translate=False;
I issued this statement
xmllint --xpath "//*[local-name()='Executable']/*[local-name()='ConnectionManagers']/*[local-name()='ConnectionManager']/*[local-name()='ObjectData']/*[local-name()='ConnectionManager']" HardestNestedWorkflow2_latest.xml
and it returned this
<DTS:ConnectionManager DTS:ConnectionString="Data Source=ADMIN-8DF005D47;Initial Catalog=AdventureWorks;Provider=SQLNCLI11;Integrated Security=SSPI;Application Name=SSIS-HardestNestedWorkflow2-{A32A68DF-3D53-4057-AF80-1B8D524F82BC}ADMIN-8DF005D47.AdventureWorks;Auto Translate=False;"/>
how can I omit the tags DTS:ConnectionManager DTS:ConnectionString
? I'm sorry I'm not much knowledgeable on using xmllint
.
Thanks in Advance
Best Answer
You have found the element, but you need the value of an attribute
@DTS:ConnnectionString
rather than the value of the element itself. Without namespaces you would pick this out with a suffix of@ConnectionString
, but since you have namespaces here,xmllint
requires you to match in the same way you've matched all your element names,@*[local-name()='ConnectionString']
. Furthermore, in order to strip the attribute name from the front of the result you need to postprocess it all withstring()
. Ugly.Output
Personally I prefer
xmlstarlet
for these kind of operations as it understands namespaces more cleanly, but ultimately it's best to use the tool that's most convenient.