I was recently tasked with creating a document repository where the documents get uploaded into a sql server database by way of a management page and pulled and displayed in various places throughout the application. I was asked to make it generic enough to be able to be tied in for other uses in the future. I also need to be able to provide a way to specify some filter data (or metadata) to identify the document and allow searching. The way I implemented this for my scenario is by creating a Document Filter Data table which has four columns, a RecordID, a DocumentID, a FilterKey, and a FilterValue. I then needed to be able to pass an array of these key/value pairs in when inserting the file into the database. In the old days I probably would have looped over the collection or array of values and made a call to the database for each set that needed inserted. Alternatively, I would have passed in some delimited string that would then have to be parsed inside the procedure (yuck). I certainly don't advocate either of these aproaches. So what's a guy to do when he needs to pass an array of values into a stored procedure? ENTER XML! I created a stored procedure that takes an xml string with the key/value pairs specified and does a batch insert by converting that xml string into a table that can be selected from, joined on, or used as any other temporary table would be in a procedure. The magic for this resides in the OPENXML function in Sql Server. You must first run sp_xml_preparedocument and finish with sp_xml_removedocument (which I will show you some examples of later). For the xmlParam I pass in a string that looks like this:
<root>
<FilterItem FilterKey="Building" FilterValue="1" />
<FilterItem FilterKey="UserType" FilterValue="Admin" />
<FilterItem FilterKey="SomeOtherType" FilterValue="" />
</root>
One of the cool things about this is by passing in an empty string for one of the values, the proc converts that to a null in the database and allows for that to be ignored by searches and be selected regardless of the value passed in, such as an "all" selection.
CREATE
PROCEDURE prDOCFilterValuesIns
(
@documentID varchar(50),
@xmlParam text
)
AS
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlParam
INSERT
INTO DOCDocumentFilterData
(DocumentID, FilterKey, FilterValue)
SELECT
@documentID,
a.FilterKey,
CASE LTRIM(RTRIM(a.FilterValue))
WHEN '' THEN NULL
ELSE a.FilterValue
END
FROM OPENXML(@DocHandle, '/root/FilterItem') WITH (FilterKey varchar(50), FilterValue varchar(100)) AS a
EXEC
sp_xml_removedocument @DocHandle
Once the you have the data in there you can also select it by running a similar procedure that also accepts an xmlParam. This time, however, it is used to create a temp table that we join on to retrieve all matching records.
CREATE
Procedure prDOCDocumentListByFilterDataSel
(
@xmlParam text
)
AS
DECLARE @elementCount int
DECLARE @docHandle int
SET NOCOUNT ON
EXEC
sp_xml_preparedocument @docHandle OUTPUT, @xmlParam
DECLARE
@FilterData TABLE
(
FilterKey varchar(50),
FilterValue VARCHAR(100)
)
INSERT
INTO @FilterData
SELECT *
FROM OPENXML(@docHandle, '/root/FilterItem') WITH (FilterKey varchar(50), FilterValue varchar(100))
SET @elementCount = (SELECT COUNT(*) FROM @FilterData)
select a.DocumentID, a.DocumentTitle, a.DocumentDescription
from DOCDocuments a with (nolock)
inner join (select distinct b.DocumentID from DOCDocumentFilterData b with (nolock)
inner join @FilterData c
on b.FilterKey = c.FilterKey and b.FilterValue = c.FilterValue
group by b.DocumentID
having count(*) >= @elementCount
) as d
on a.DocumentID = d.DocumentID
ORDER BY a.DocumentTitle
EXEC
sp_xml_removedocument @docHandle
[This post was originally posted at my DevAuthority.com blog.
http://www.devauthority.com/blogs/dbalzer/default.aspx Go to
http://www.devauthority.com to get your own .Net blog.]