February 8, 2012 at 4:27 pm
Looking for SQL (2008) query to perform a request from a table to return back for me a HTML formatted select combobox.
The following query works, and returns back the following results:
Query
DECLARE @parentID int
DECLARE @selected int
SET @parentID=0
SET @selected=2
SELECT '0' AS '@value', null as '@SELECTED', '---select one---' as 'data()'
union all
SELECT pkCatID as '@value',
case pkCatID when @selected then '1' else null end as '@SELECTED',
CatNameLU.strCatName as 'data()'
FROM CatNameLU
WHERE intCatParentID=@parentID
ORDER BY [data()] ASC
FOR XML PATH('option'), TYPE
Results:
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
Looking for:
I would like the final results to look something like this, but unclear on how to get the select portion around the currently working options.
<select id='somename' name='somename' onclick='dosomething();'>
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
</select>
I'd like to know how to have this finalized string returned, say placed within a varchar?
Any help is greatly appreciated. I have been banging my head trying to figure this out, when i should have just asked the pros for the answer.
Heads up thank you
Paul
February 8, 2012 at 7:59 pm
Hi Paul,
It would have been nicer if you could provide some sample data and the schema of your table. Anyway looking at your select query I created a sample table. Please check is it something like this that you were looking for ?
DECLARE @parentID INT
DECLARE @selected INT
SET @parentID=0
SET @selected=2
DECLARE
@id AS VARCHAR(20) = 'somename'
,@name AS VARCHAR(20) = 'somename'
,@onclick AS VARCHAR(20) = 'dosomething();'
--====== Generate Sample Data ==============
DECLARE @CatNameLU AS TABLE(
intCatParentID INT,
pkCatID INT,
strCatName VARCHAR(20)
)
INSERT INTO @CatNameLU (intCatParentID,pkCatID, strCatName)
SELECT 0,1,'Root 1' UNION
SELECT 0,2,'Root 2' UNION
SELECT 0,3,'Root 3'
--====== Logic ========
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE
Output
-------
<select id="somename" name="somename" onclick="dosomething();">
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
</select>
I hope this will solve your issue.
--------
Manjuke
http://www.manjuke.com
February 9, 2012 at 8:08 am
Apologies before for not supplying test data. I will definitely take this into consideration for future posts.
Tested out your code and it works 100% PERFECT! THANK YOU SO MUCH.
One quick question. When i run the query, i see the query result as a single column, but its listed as 'No column name. If I am making an ODBC call, and I want to retrieve this information as a string, how could i assign a column name to the column name, or return the value as a string?
Last question i promise
Thanks so much!
February 9, 2012 at 8:31 am
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
--------
Manjuke
http://www.manjuke.com
February 9, 2012 at 8:34 am
manjuke (2/9/2012)
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
Why have you done TOP 100 PERCENT ? It's superfluous, you'd get the same result without it: -
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
February 9, 2012 at 8:37 am
100% Spot on! Thank you so much. Take care and look forward to more assistance!
I bow before the Grasshopper, as the student still continues his lessons!
February 9, 2012 at 9:08 am
I need to ask...
Why in god sake are you returning a html control from the database?
February 9, 2012 at 9:25 am
Cadavre (2/9/2012)
manjuke (2/9/2012)
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
Why have you done TOP 100 PERCENT ? It's superfluous, you'd get the same result without it: -
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
Thanks for pointing out.. I didn't notice that too.. Sorry
--------
Manjuke
http://www.manjuke.com
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy