Query SQL to return back a XML string created Select Combobox

Question

Query SQL to return back a XML string created Select Combobox

paul.mozolewski

SSC Enthusiast

Points: 131
More actions
February 8, 2012 at 4:27 pm

#264154

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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Manjuke SSChasing Mays Points: 629 More actions · Answer 1

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

paul.mozolewski SSC Enthusiast Points: 131 More actions · Answer 2

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!

Manjuke SSChasing Mays Points: 629 More actions · Answer 3

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

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'

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

paul.mozolewski SSC Enthusiast Points: 131 More actions · Answer 5

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!

jcb SSCertifiable Points: 6574 More actions · Answer 6

I need to ask...

Why in god sake are you returning a html control from the database?

Manjuke SSChasing Mays Points: 629 More actions · Answer 7

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

Query SQL to return back a XML string created Select Combobox

Cookies on SQLServerCentral