Return Concatenated Substrings From Column In SQL

Question

I'm attempting to create a SQL view that will return a concatenated value of substrings from within a column in a table.

In my example, There is a column titled 'DefDetails' within a table named 'TrebuchetSettings', which contains an XML list of values that I need concatenated together.

The DefDetails column will hold a value like the one below, per record:

<Trebuchet> <FolderSetDef ID="9365da81288308c9c57aba483f83d2469a5da9ecba" Name="ReportDef" Version="1.0" SubType="" Scope="Global" Culture="Invariant" View="(None)"> <Alias /> <Description /> <Owner>934ec7a1701c451ce57f2c43bfbbe2e46fe4843f81</Owner> <FolderList> <FolderDef ID="93af31dc1b3238241be33549ba8f8239b377767680" Name="Yearly Reports" ParentID="93af31a36cf8232f44265b40f9a1cd14d1e7000813" Scope="Core" /> <FolderDef ID="93af31a36cf8232f44265b40f9a1cd14d1e7000813" Name="CSM Management Reports" ParentID="Root" Scope="Core" /> </FolderList> </FolderSetDef> </Trebuchet>

Using a SQL statement, I need to query the TrebuchetSettings table and return a concatenated list of the 'Name' fields within the 'FolderDef' nodes in the XML above, separated by a '/' character, with FolderDef's which ID's match a ParentID of subsequent FolderDef's listed at the beginning of the string, so that the concatenated structure represents a folder structure.

As there are other types of records in this table, my query currently consists of the following, to identify the records that I need to pull these substrings from:

SELECT *
FROM TrebuchetSettings
WHERE DefType = 'FolderSetDef'
AND DefDetails LIKE '%(Folder ID)%'

In the example SQL above, Folder ID is a 42 character ID that will be compared to a value from another table to match the ID of one of the Folder Defs in the XML.

Unfortunately I do not have a starting set of code for this, as I do not have experience working with substrings of a column in SQL and don't know where to start.

Answer 1

What you are trying to do might be very slow if there is a lot of data... In this case it would be a good advise to administer a (triggered?) side table where you hold your ParentIDs in an indexed column together with a key to your XML containing row. This is - in other words - something like a self done index...

Just to make this clear: You have a table with several IDs (long strings). Then you have to find all XML entries which contain this value as "ParentID" within "/FolderDef". If you find one, you want all the Names of the specific DefDetails concatenated. That means: For any ID in your second table you'll have to scan all DefDetails over and over... A LIKE search with a % at the beginning will be very slow. The XML-method .exist() should be faster...

You might try this:

I declare a table variable to mock up your settings table with two entries:

DECLARE @TrebuchetSettings TABLE(DefDetails XML);
INSERT INTO @TrebuchetSettings VALUES
(N'<Trebuchet>
    <FolderSetDef ID="9365da81288308c9c57aba483f83d2469a5da9ecba" Name="ReportDef" Version="1.0" SubType="" Scope="Global" Culture="Invariant" View="(None)">
        <Alias />
        <Description />
        <Owner>934ec7a1701c451ce57f2c43bfbbe2e46fe4843f81</Owner>
        <FolderList>
            <FolderDef ID="93af31dc1b3238241be33549ba8f8239b377767680" Name="Yearly Reports" ParentID="93af31a36cf8232f44265b40f9a1cd14d1e7000813" Scope="Core" />
            <FolderDef ID="93af31a36cf8232f44265b40f9a1cd14d1e7000813" Name="CSM Management Reports" ParentID="Root" Scope="Core" />
        </FolderList>
    </FolderSetDef>
</Trebuchet>')
,(N'<Trebuchet>
    <FolderSetDef ID="SomeOther" Name="ReportDef" Version="1.0" SubType="" Scope="Global" Culture="Invariant" View="(None)">
        <Alias />
        <Description />
        <Owner>OtherOwner</Owner>
        <FolderList>
            <FolderDef ID="Other first ID" Name="Yearly Reports" ParentID="Other ParentID" Scope="Core" />
            <FolderDef ID="Other second ID" Name="CSM Management Reports" ParentID="Root" Scope="Core" />
        </FolderList>
    </FolderSetDef>
</Trebuchet>');

Define your second table with one existing ParentID and one not existing

DECLARE @YourOtherTable TABLE(FolderID VARCHAR(42));
INSERT INTO @YourOtherTable VALUES
 ('93af31a36cf8232f44265b40f9a1cd14d1e7000813')
,('Some not existing');

Now find all records with a given FolderDef-ParentID and list all the Name-attributes as /-separated list

SELECT ot.FolderID
      ,concatenated.Names
FROM @YourOtherTable AS ot
CROSS JOIN @TrebuchetSettings AS s
CROSS APPLY
(
    SELECT STUFF(
    (
    SELECT '/' + A.NameAttr.value('@Name','varchar(max)')
    FROM DefDetails.nodes('/Trebuchet/FolderSetDef/FolderList/FolderDef') AS A(NameAttr)
    FOR XML PATH('')
    ),1,1,'')
) AS concatenated(Names)
WHERE s.DefDetails.exist('/Trebuchet/FolderSetDef/FolderList/FolderDef[@ParentID=sql:column("ot.FolderID")]')=1

The result for this would be:

93af31a36cf8232f44265b40f9a1cd14d1e7000813  Yearly Reports/CSM Management Reports