Long Complex UNION (XML, etc) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Long Complex UNION (XML, etc)

Author

Topic

JimmyFo
Starting Member

11 Posts

Posted - 2006-10-04 : 16:16:07

Hi, please bear with me here :) I have a stored procedure with three parameters, two XML files and a contains clause (for simplicity's sake, assume it is one word).

1) XML1: a list of files that have entries in the database, that is being sent over from another database they are in
2) XML2: a list of allowed file types ("extensions") we can return
3) the contains clause

Now, the error may not be with the contains like I think it may be - basically it is only returning the documents it finds in the XML document "@xmlFileList", ignoring the other rows found in the second SELECT.

I am doing a union here. The first SELECT is all file types in the database that have matching names to the XML files and extensions sent in. This is UNIONed with the second SELECT, which is all files that DON'T have the same names (to remove duplicates) and where it CONTAINS certain keywords in the fulltext search. I need to compile a list and return it. However, if the description column and the keyword column in the row do not match, the result is ignored! I.E. if a keyword is found in one, but not the other, the row is not picked.

Why is this?

Here is the simplified SQL:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[GetAssetList]
(
	@xmlFileList varchar(1000),
	@strContainsClause varchar(1000),
	@xmlFileTypes varchar(1000)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @intDocHandle int,
			@intFileTypeHandle int,
			@err int

...--prepare XMLdocs

	SELECT DISTINCT
		AssetsMaster.AssetMasterUID,
		AssetsMaster.Extension,
		AssetsMaster.Description,
		AssetsMaster.Keywords
	FROM
		AssetsMaster, 
		OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME  varchar(256)) AS x,
		OPENXML (@intFileTypeHandle, '/NewDataSet/Table',2) WITH (FILETYPE varchar(256)) AS y,
		SupportedFiles
	WHERE
		AssetsMaster.AssetFileName = x.FILENAME
		AND AssetsMaster.Extension = SupportedFiles.Extension
		AND AssetsMaster.Extension = y.FILETYPE
	
	UNION

	SELECT DISTINCT
		AssetsMaster.AssetMasterUID,
		AssetsMaster.Extension,
		AssetsMaster.Description,
		AssetsMaster.Keywords
	FROM
		AssetsMaster, 
		OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME  varchar(256)) AS x,
		OPENXML (@intFileTypeHandle, '/NewDataSet/Table',2) WITH (FILETYPE varchar(256)) AS y,
		SupportedFiles
	WHERE
		AssetsMaster.AssetFileName <> x.FILENAME
		AND CONTAINS ((Description, Keywords), @strContainsClause)
		AND AssetsMaster.Extension = SupportedFiles.Extension
		AND AssetsMaster.Extension = y.FILETYPE

	ORDER BY AssetsMaster.Downloads DESC

	EXEC sp_xml_removedocument @intDocHandle
	EXEC sp_xml_removedocument @intFileTypeHandle
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Sample input:

exec GetAssetList @xmlFileList='<NewDataSet>
  <Table>
    <FILENAME>test.doc</FILENAME>
  </Table>
</NewDataSet>',@strContainsClause='"manage" 
',@xmlFileTypes='<NewDataSet><Table><FILETYPE>DOC</FILETYPE></Table><NewDataSet>'

Any ideas?

Thanks,
James

Kristen
Test

22859 Posts

Posted - 2006-10-05 : 01:56:53

"CONTAINS ((Description, Keywords), @strContainsClause)"

I didn't think (SQL2000 !!) that you could have more than one column specified for CONTAINS (well, unless you use "*" for all columns registered for full-text search)

Personally I would have used JOINs rather than the WHERE clause approach, but I don't suppose that makes any difference to the outcome - although the NOT EQUALS (AssetsMaster.AssetFileName <> x.FILENAME) may be less efficient that using an OUTER JOIN with a WHERE x.FILENAME IS NULL or a NOT EXISTS clause - but none of that is the problem at hand!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources