--Run against vault store DB

DECLARE @ArchiveName nvarchar(75)
DECLARE @ArchiveID nvarchar(75)
DECLARE @FolderID nvarchar(75)
DECLARE @Idtransaction nvarchar(40)

SET @ArchiveID = NULL 
SET @ArchiveName = NULL
SET @FolderID = NULL
SET @Idtransaction = NULL


Select
Archive.ArchiveName,
Idtransaction,
SP.SavesetIdentity,
FolderPath,
CASE 

        WHEN LEFT(sp.properties,2) = '<?' THEN SUBSTRING(properties, (CHARINDEX('<filename>', properties)+10), (CHARINDEX('</filename>', properties)-(CHARINDEX('<filename>', properties)+10)))
        -- Uncomment below if version is 11.x or higher  
	-- WHEN LEFT(sp.properties,6) = 'FSA/3/' THEN SUBSTRING(properties, 7, CHARINDEX('/', properties, 7)-7)
        -- WHEN LEFT(sp.properties,6) = '<C v="' THEN SUBSTRING (properties, 7, CHARINDEX('|',properties, 7)-7)
	-- WHEN LEFT(sp.properties,6) = '<L v="' THEN SUBSTRING (properties, 7, CHARINDEX('|',properties, 7)-7)

 
END as FileName,
SP.Properties 
FROM  
EnterpriseVaultDirectory.dbo.root r1

     JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
     JOIN EnterpriseVaultDirectory.dbo.archive on r1.RootIdentity = Archive.RootIdentity
     JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
     JOIN Vault on r2.VaultEntryId = Vault.Vaultid 
     JOIN Saveset ss on Vault.VaultIdentity = ss.VaultIdentity
     JOIN SavesetProperty sp on ss.savesetidentity = sp.SavesetIdentity

WHERE 

     r1.VaultEntryid= ISNULL(@ArchiveID, r1.VaultEntryid)
     AND ArchiveName = ISNULL(@ArchiveName, ArchiveName)    
     AND r2.VaultEntryid = ISNULL(@FolderID, r2.VaultEntryId)   
     AND ss.IdTransaction = ISNULL(@Idtransaction, ss.IdTransaction)
       --order by SavesetIdentity