0

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

0

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 with string(). Ugly.

xmllint --xpath "string(//*[local-name()='Executable']/*[local-name()='ConnectionManagers']/*[local-name()='ConnectionManager']/*[local-name()='ObjectData']/*[local-name()='ConnectionManager']/@*[local-name()='ConnectionString'])" file.xml

Output

 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;

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.

xmlstarlet sel xmlns:DTS="www.microsoft.com/SqlServer/Dts" -t -v '/DTS:Executable/DTS:Property' -n file.xml
8

xmlstarlet sel xmlns:DTS="www.microsoft.com/SqlServer/Dts" -t -v '/DTS:Executable//DTS:ConnectionManager/@DTS:ConnectionString' -n file.xml
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;
| improve this answer | |
  • Thank you sir. This really helped me a lot. – Kelvin Feb 19 at 15:56
  • one more thing, I'd like to ask, where can I find some documentations like this ? I'm googling it but some solutions I found ain't compatible to what I want. You hit the right note there :) – Kelvin Feb 19 at 15:58
  • There's quite a lot here on Unix & Linux but also on Stack Overflow. Generally search on the xml tag. – roaima Feb 19 at 16:21
  • Thanks. I was able to find some documentations however, majority of all I'm seeing is xmllint syntaxes. I'd be interested to see something like using [local-names] within xmllint to navigate thru xml with namespaces (in fact, i only got this idea here in stackexchange) I was wondering if there's a book I can buy or simply a website with all of these "how to-s" – Kelvin Feb 21 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.