using Search object¶
Example to demonstrate the use of searching of objects from SQL
search for text in name or title
search for text in a specific property
search filter ‘contain’ text or ‘equals’ text
-- -----------------------------------------------------------
-- Searching for text in the name or title of any object in any class
-- -----------------------------------------------------------
-- Option 1: channel search result to a temporary table. this allows for searches to be recorded. Note the temporary tables has a limited life span as it will auto delete when the user disconnects from SQL. the entry in the search log will not be removed.
DECLARE @XMLOutPut XML
,@TableName VARCHAR(200);
EXEC dbo.spMFSearchForObject @ClassID = 78 -- the class MFID: this can be obtained from select Name, MFID from MFClass
,@SearchText = 'A' -- any text value, this can be a part text. It does not cater for wildcards
,@Count = 5 -- used to restrict the number of search result returns.
,@Debug = 0
,@OutputType = 1 -- set to 1 to channel output to a table
,@XMLOutPut = @XMLOutPut OUTPUT -- is null
,@TableName = @TableName OUTPUT;
-- used in subsequent processing to process the search result.
-- show temp table name
SELECT @TableName AS TableName;
-- view search result
SELECT *
FROM dbo.MFSearchLog AS msl;
GO
-- Option 2: channel search result to a XML record. This is usefull if a specific property must be extracted out of the search result.
DECLARE @XMLOutPut XML
,@TableName VARCHAR(200);
EXEC dbo.spMFSearchForObject @ClassID = 78 -- the class MFID: this can be obtained from select Name, MFID from MFClass
,@SearchText = 'A' -- any text value, this can be a part text. It does not cater for wildcards
,@Count = 5 -- used to restrict the number of search result returns.
,@Debug = 0
,@OutputType = 0 -- set to 0 to channel output to XML
,@XMLOutPut = @XMLOutPut OUTPUT --used in subsequent processing
,@TableName = @TableName OUTPUT;
-- is null
-- view XML record
SELECT @XMLOutPut AS XMLOutput;
-- get entire XML by record
-- as XML
SELECT T.N.query('.')
FROM @XMLOutPut.nodes('/form/Object') AS T(N);
-- as a table
SELECT t.c.value('(../@objectId)1', 'INT') AS objectId
,t.c.value('(@propertyId)1', 'INT') AS propertyId
,mp.Name AS propertyName
,t.c.value('(@propertyValue)1', 'NVARCHAR(100)') AS propertyValue
FROM @XMLOutPut.nodes('/form/Object/properties') AS t(c)
INNER JOIN dbo.MFProperty AS mp
ON mp.MFID = t.c.value('(@propertyId)1', 'INT');
GO
-- -----------------------------------------------------------
-- Search for text in a specific property
-- -----------------------------------------------------------
/*
note when a value is searched for a valuelist property, you need to specify the MFID of the valuelist item to search.
*/
DECLARE @XMLOutPut XML
,@TableName VARCHAR(200);
EXEC dbo.spMFSearchForObjectbyPropertyValues @ClassID = 78 -- the class MFID
,@PropertyIds = '1090' --comma delimited list of property MFID's to search
,@PropertyValues = '1' -- comma delimited list of text values to search. Note the Property IDs are paired with the search values.
,@Count = 5 -- int
,@OutputType = 0 -- int
,@XMLOutPut = @XMLOutPut OUTPUT -- xml
,@TableName = @TableName OUTPUT;
-- varchar(200)
-- view XML record
SELECT @XMLOutPut AS XMLOutput;
-- get entire XML by record
-- as XML
SELECT T.N.query('.')
FROM @XMLOutPut.nodes('/form/Object') AS T(N);
-- as a table
SELECT t.c.value('(../@objectId)1', 'INT') AS objectId
,t.c.value('(@propertyId)1', 'INT') AS propertyId
,mp.Name AS propertyName
,t.c.value('(@propertyValue)1', 'NVARCHAR(100)') AS propertyValue
FROM @XMLOutPut.nodes('/form/Object/properties') AS t(c)
INNER JOIN dbo.MFProperty AS mp
ON mp.MFID = t.c.value('(@propertyId)1', 'INT');
-- -----------------------------------------------------------
-- Search using filter for contain or equal
-- the filter only applies to search by property Value
-- by default it is set to @IsEqual = 1
-- -----------------------------------------------------------
GO
SELECT MFID FROM dbo.MFClass AS mc WHERE name = 'customer'
SELECT * FROM dbo.MFCustomer AS mc
SELECT MFID FROM dbo.MFProperty AS mp WHERE mp.ColumnName = 'Address_Line_1'
-- '6575 Madison Avenue'
-- select a part of the property and set @IsEqual = 0
-- the result will return all the object where the property value is contained in the property
DECLARE @XMLOutPut XML
,@TableName VARCHAR(200);
EXEC dbo.spMFSearchForObjectbyPropertyValues @ClassID = 78 -- int
,@PropertyIds = '1073' -- nvarchar(2000)
,@PropertyValues = 'Avenue' -- nvarchar(2000)
,@Count = 5 -- int
,@OutputType = 0 -- int
,@IsEqual = 0 -- int
,@XMLOutPut = @XMLOutPut OUTPUT -- xml
,@TableName = @TableName OUTPUT -- varchar(200)
SELECT @XMLOutPut
GO
-- select the exact property value of a record of the property and set @IsEqual = 1
-- the result will return only those with an exact match.
DECLARE @XMLOutPut XML
,@TableName VARCHAR(200);
EXEC dbo.spMFSearchForObjectbyPropertyValues @ClassID = 78 -- int
,@PropertyIds = '1073' -- nvarchar(2000)
,@PropertyValues = '6575 Madison Avenue' -- nvarchar(2000)
,@Count = 5 -- int
,@OutputType = 0 -- int
,@IsEqual = 1 -- int
,@XMLOutPut = @XMLOutPut OUTPUT -- xml
,@TableName = @TableName OUTPUT -- varchar(200)
SELECT @XMLOutPut