Urgent help on Comparing multivalue columns

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 2005 Forums

Transact-SQL (2005)

Urgent help on Comparing multivalue columns

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-11 : 05:40:28

I have xml column stored in table and xml node value for multiple id will be stored as
<mid value="33,55,32">

and in stored procedure.. user passes parameter string of multiple IDs as "32,55" or it may be "33"

so how can i compare to filter records with above values.

my query is like


@parameter from SP will pass as "32,55" or "33"
SELECT XmlCriteria.value(''(/Filter/MID/@value) [1]'',''varchar(500)'') MIDs	
FROM temp_Criteria


-------------
ids     mids
-------------
1	 240, 56
2	 44	
3	 120

My problem is.. as below


-- EXEC tempproc @mids = '240' 
CREATE PROC tempproc
@mids varchar(50)
AS
BEGIN
	SELECT XmlCriteria.value(''(/Filter/MID/@value) [1]'',''varchar(500)'') MIDs	
	FROM temp_Criteria
	 "???" WHERE XmlCriteria.value('(/SalesOrder/SalesOrderFilter/ENTITY/OEMID/@value)[1]', 'varchar(500)') IN (SELECT Data FROM dbo.fnSplitString('240,56',','))
END

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-11 : 07:32:30

Is there anyone know about it??

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-11-11 : 11:19:51

Please, provide XmlCriteria xml structure

rupspawar
Starting Member

3 Posts

Posted - 2010-11-11 : 12:10:29

SELECT ID,XmlCriteria.value(''(/Filter/MID/@value) [1]'',''varchar(500)'') MIDs, data
FROM temp_Criteria
cross apply dbo.fnSplitString(XmlCriteria.value(''(/Filter/MID/@value) [1]'',''varchar(500)'') )
where data not in (240,56)

please let me know if it works

Thanks
Rupesh

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-12 : 00:42:41

XML can be like below:


<Criteria>
    <MID Name="Hewlett Packard, ViewSonic" DataType="string" value="240, 56" />    
</Criteria>

I have comma seperated IDs in xml.

Thank you for reply..

Above cross apply does not work..

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 04:37:39

Will there always going to be 2 values in comma seperated format in the xml for Name and value?

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-12 : 05:01:34

Yes.. in commma seperated value can be multiple IDs..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources