T-SQL - XML to SQL Server Table

This is an enhancment to the answer of djangojazz. Don't accept this, it's just a copy (but you may vote up, if you like it ;-) ...

By using WITH XMLNAMESPACES you can avoid the multiple declaration of the namespace:

WITH XMLNAMESPACES (N'http://DTS' as DTS )
SELECT 
    x.vals.query('.') AS NodesAsExtracted
,   x.vals.value('@DTS:CreationName', 'Varchar(255)') AS CreationName
,   x.vals.value('@DTS:ObjectName', 'Varchar(255)') AS ObjectName
,   y.vals.value('@DTS:ConnectionString', 'Varchar(255)') AS ConnectionString
,   x.vals.value('@DTS:ColumnType', 'Varchar(255)') AS ColumnType
,   x.vals.value('@DTS:MaximumWidth', 'Varchar(255)') AS MaximumWidth
from @XML.nodes('/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn') AS x(vals)
    CROSS APPLY @XML.nodes('/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager') AS y(vals) 

Of course, you can also use a general-purpose language to convert XML content to tabular format for database import or delimited file export.

Great as it is, SQL is a special-purpose language and not as fluid or dynamic as Java, C#, Python, PHP, Perl, VB, and still others which also carry libraries to run XPath, XSLT, and other XML-specific tasks. Additionally, these languages can connect to any database to retrieve BLOB data.

For future readers, below are open-source examples using the OP's data needs. You will notice use of the positional brackets with [] in xpaths, allowing more DTS:ConnectionManager elements:

Python (using lxml library)

import os
import lxml.etree as ET

cd = os.path.dirname(os.path.abspath(__file__))

xmlfile = 'DTSfile.xml'
dom = ET.parse(os.path.join(cd, xmlfile))
root = dom.getroot()

nodexpath = dom.xpath("//DTS:ConnectionManager", namespaces=root.nsmap)
dataline = []

def checkPath(xpathstr):    
    if dom.xpath(xpathstr, namespaces=root.nsmap) == []:
        return ''
    else:
        return dom.xpath(xpathstr, namespaces=root.nsmap)[0]

for i in range(1,len(nodexpath)+1):
    if i % 2 == 0: continue

    dataline = []    
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/@DTS:CreationName'.format(i)))
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/@DTS:ObjectName'.format(i)))
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString'.format(i)))    
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:MaximumWidth'.format(i)))
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:DataType'.format(i)))
    dataline.append(checkPath('//DTS:ConnectionManager[{0}]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:ObjectName'.format(i)))

    print(dataline)

['FLATFILE', 'FF', 'Test.csv', '', '11', 'TestCN']
['FLATFILE', 'FF2', 'Test2.csv', '', '11', 'TestCN2']

PHP (using simple_xml object)

$cd = dirname(__FILE__);

$xml = simplexml_load_file($cd.'/DTSfile.xml');
$xml->registerXPathNamespace('DTS', 'www.microsoft.com/SqlServer/Dts');

$values = [];
$node = $xml->xpath('//DTS:ConnectionManager');

function checkPath($xml, $xpathstr) {         
     $path = $xml->xpath($xpathstr);
     foreach ($path as $value) {
      if (count($path) > 0) {
           foreach($path as $value) {           
           return $value;         
           }
      }   
      else {
           return '';
      }       
     }         
}
$i = 1;

foreach ($node as $n){         
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/@DTS:CreationName');
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/@DTS:ObjectName');
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString');    
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:MaximumWidth');
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:DataType');
     $values[] = checkPath($xml, '//DTS:ConnectionManager['.$i.']/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:ObjectName');     

     if ($values[1] != "") {
      echo implode(", ", $values)."\n";
     }
     $i++;
     $values = [];         
}

FLATFILE, FF, Test.csv, , 11, TestCN
FLATFILE, FF2, Test2.csv, , 11, TestCN2

R (using xml library)

library(XML) 

setwd("C:\\Path\\To\\Working\\Directory")

doc<-xmlParse("DTSfile.xml")

nodes <- as.list(xpathSApply(doc, '//DTS:ConnectionManager'))

checkPath <- function (xpathstr) {
  if (length(as.list(xpathSApply(doc, xpathstr))) > 0) {
    return(as.list(xpathSApply(doc, xpathstr)))
  } else {
    return("")
  }
}

for (i in (1:length(nodes))) {  
  if (i %% 2) {
    data <- checkPath(sprintf('//DTS:ConnectionManager[%d]/@DTS:CreationName', i))
    data <- append(data, checkPath(sprintf('//DTS:ConnectionManager[%d]/@DTS:ObjectName', i)))
    data <- append(data, checkPath(sprintf('//DTS:ConnectionManager[%d]/DTS:ObjectData/DTS:ConnectionManager/@DTS:ConnectionString', i)))
    data <- append(data, checkPath(sprintf('//DTS:ConnectionManager[%d]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:MaximumWidth', i)))
    data <- append(data, checkPath(sprintf('//DTS:ConnectionManager[%d]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:DataType', i)))
    data <- append(data, checkPath(sprintf('//DTS:ConnectionManager[%d]/DTS:ObjectData/DTS:ConnectionManager/DTS:FlatFileColumns/DTS:FlatFileColumn/@DTS:ObjectName', i)))

    print(paste(data, collapse = ', '))
  }

}

[1] "FLATFILE, FF, Test.csv, , 11, TestCN"
[1] "FLATFILE, FF2, Test2.csv, , 11, TestCN2"