SQLTeam.com | Weblogs | Forums

Get from XML all ObjectNames LIKE 'MF%' OR 'MC%'


#1

Hi,

how can i get all Values from a XML-File (with tsql)
WHERE ObjectNames LIKE 'MF%' OR 'MC%' ?
(The Source-XML is a SSIS-Dtsx-Package).
Regards
Nicole

<DTS:Executable DTS:ExecutableType="SSIS.Pipeline.2">
    <DTSroperty DTS:Name="ExecutionLocation">0</DTSroperty>
    <DTSroperty DTS:Name="ExecutionAddress"></DTSroperty>
    <DTSroperty DTS:Name="DelayValidation">0</DTSroperty>
   .....
    <DTSroperty DTS:Name="ObjectName">MFK_LOG_ERRORS</DTSroperty>

**K_LOG_ERRORS

MyCode

SELECT Props.Prop.query('.') as PropXml
, Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
string(./@p1:Name)','nvarchar(max)') as PropName
, Props.Prop.value('.', 'nvarchar(max)') as PropValue
FROM (
SELECT CAST(pkgblob.BulkColumn AS XML) pkgXML
FROM OPENROWSET(bulk 'C:\Program Files\Microsoft SQL Server\110\DTS\Packages\Work\SWH_EXT_FKR.dtsx',single_blob) AS pkgblob
) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
/DTS:Executable/DTSroperty') Props(Prop)


#2

is that the whole truth and nothing but the truth on that ssis package xml. there seems to be something missing


#3
WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
select Props.Prop.value('.', 'nvarchar(max)') as PropValue,
       Props.Prop.value('string(./@DTS:Name)','nvarchar(max)') as PropNameNS
FROM (
		SELECT CAST(pkgblob.BulkColumn AS XML) pkgXML
		FROM OPENROWSET(bulk 'C:\XML\SWH_EXT_FKR.dtsx',single_blob) AS pkgblob
	) t
CROSS APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:Executables/DTS:Executable/DTSroperty') Props(Prop)
where  Props.Prop.value('.', 'nvarchar(max)')  like '%MFK%'