I’m using DB Browser for Sqlite as my GUI DB tool on an RM10 database. For safety copied the database into a sandbox folder and perform actions there. I’m trying to identify the Table/Column names where a citation’s "Item of interest’ value is saved. RM10’s search tool doesn’t appear to examine that content, so a SQL tool was my next best option.
In the Fields column of CitationTable. It contains all the values of the Citation level variables in XML format.
Have you visited sqlitetoolsforrootsmagic.com and, especially, the page:
RM10 Data Dictionary?
Item of Interest is a field in some of RM’s built-in source templates. As Tom mentioned, such data is stored in XML strings. In turn, the XML strings are stored in BLOB fields (binary fields) which need to be cast to text to be able to see them. Even after casting to text, the XML fields are very difficult to parse out using SQL.
Try something like the following to see all of your Item of Interest items. The overall Fields column will contain XML data that looks something like <Fields>…</Fields> Then within the Fields data will be a series of Field items that look something like <Field>…</Field> Finally, within each Field item will be data that looks something like <Name>Item of Interest</Name> <Value>your actual item of interest data</Value>
SELECT CAST(C.Fields AS TEXT) AS TextFields
FROM CitationTable AS C
WHERE TextFields LIKE '%Item of Interest%'
You will have to do considerably more work to make a nice report out of the results. And please do move further discussion to Tom’s SQLite site.
