Windows Workflow 4 stores its persisted bookmarks inside of the [System.Activities.DurableInstancing].[InstancesTable] table in the column [BlockingBookmarks]

So I was having an issue in my OpenAccess Domain Model getting a list of items in order to run queries on (give me all bookmarks pending across instances for X user...for example)

So that led me to make this...it'll dump bookmarks into this format
[ID][BOOKMARKNAME][CREATED]
Such that each row denoted a pending bookmark, and the ID column is not unique (lets say there's 4 bookmarks for the same InstanceID, thats 4 rows)

getwf4bookmarks.sqlView on GitHub
-- =============================================
-- Author:      Steve
-- Create date: Auguest 2, 2010
-- Description: Converts the Pending Bookmarks into rows
-- =============================================
CREATE PROCEDURE [dbo].[workflow_Get_Bookmarks]
    @instanceID uniqueidentifier = NULL
AS
BEGIN
    SET NOCOUNT ON;
     

DECLARE @Bookmarks TABLE(
    ID uniqueidentifier,
    Bookmark varchar(1000),
    Created datetime
)
 
Declare @ID as uniqueidentifier
Declare @Bookmark as nvarchar(1000)
Declare @Created as datetime

Declare BookmarkCursor CURSOR FAST_FORWARD FOR

SELECT [Id],[BlockingBookmarks], [CreationTime]
FROM [authdb].[System.Activities.DurableInstancing].[InstancesTable]
ORDER BY [CreationTime] desc

OPEN BookmarkCursor
FETCH NEXT FROM BookmarkCursor
INTO @ID, @Bookmark, @Created

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @Bookmarks(ID, Bookmark, Created)
        SELECT @ID, REPLACE(string,']',''), @Created
        FROM [authdb].[dbo].[SplitToRows] (
           '['
          ,@Bookmark)

     FETCH NEXT FROM BookmarkCursor
     INTO @ID, @Bookmark, @Created
END

CLOSE BookmarkCursor
DEALLOCATE BookmarkCursor
 

IF @instanceID IS NULL
    BEGIN
        SELECT *
        FROM @Bookmarks
        WHERE (Bookmark <> null) or (Bookmark <> '')
    END
ELSE
    BEGIN
        SELECT *
        FROM @Bookmarks
        WHERE (Bookmark <> null) or (Bookmark <> '') AND
        ID = @instanceID
    END



 END


GO
Ok, now there's one other component required, and that's a table function to convert the initial bookmark string ([value][value][value]) into living on their own rows


//
/ This is to help convert Instance Bookmarks to a table /
//
CREATE FUNCTION [dbo].[SplitToRows] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn as id, LTRIM(RTRIM(SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) AS string
    FROM Pieces
  )


GO