XML Select Example (Legacy)
The XML Select control works in conjunction with either an External Data Source Control or a Database Get control to render the results of a query as a grid or drop down list. Items selected in the grid can then be used again in further queries to present related data.
-
Create a variable to hold the results of your query.
-
Create a SQL query that selects the data you want to render on the script page.
-
Embed the XML tags within your query.
-
Write the output of the query to a variable that the XML Select will use to display the resulting data.
In the following example, we want to display the number of Reps available in each county on our script page.
The following SQL Statement is used to count the reps in each county:
This gives us the table we wish to display on our script page, however we now need to embed XML into the SQL statement in order to define how it will be displayed, the result of which will look like this:
<xml>
<columns>
<column><name>County</name></column>
<column><name>Reps</name></column>
</columns>
<rows>
<row><county>Bristol</county><No_Reps>6</Reps></row>
<row><county>Essex</county><No_Reps>6</Reps></row>
<row><county>Leics</county><No_Reps>3</Reps></row>
<row><county>Suffolk</county><No_Reps>11</Reps></row>
<row><county>Yorkshire</county><No_Reps>6</Reps></row>
</rows>
</xml>
We now have to combine the two in a statement that results in a string containing the XML output that the XML Select can render in our script page. The statement will be placed in either a Database Get control, or an External Data Source control depending on when you want the query to run.
|
Step
|
Code
|
Description
|
|
1
|
BEGIN
|
|
|
2
|
SET NOCOUNT ON
|
|
|
3
|
Declare @MyRowVariable nvarchar(max)
Declare @MyColumnVariable nvarchar(max)
|
Declare your variables. These can be called anything you like.
|
|
4
|
Set @MyRowVariable = ''
Set @MyColumnVariable = ''
|
Set the variables to equal nothing to ensure they are empty each time the query runs.
|
|
5
|
Set @MyColumnVariable = @MyColumnVariable+'<column><name>County</name><reference>True</reference>
<display></display></column><column><name>Reps</name><reference>False</reference>
<display></display></column>'
|
Set the column Names. One column, and only one column MUST be a reference (unique) value.
|
|
6
|
Set @MyRowVariable = @MyRowVariable+(Select county as County, count(id) as Reps from TrainingData group by county for xml path('row'))
|
Set the row variable contents using your SQL statement.
|
|
7
|
END
|
End the Statement
|
|
8
|
Select '<xml><columns>' + @MyColumnVariable + '</columns><rows>' + @MyRowVariable + '</rows></xml>' as anythingyoulike
|
This last row "glues" the opening tags, column variable, row variable and closing tags together and saves it in a variable. It doesn't matter what the variable is called as we pull the data into the Database Get or External Data Source control in your script.
|
You must make sure that the Column Names you specify in step 5, exactly match the column names in your SQL statement. In the example above, the database field "county" does not match the required column name of "County". In this case, this has been overcome by giving the column an alias in the SQL statement to which matches the column specified in the XML. Alternatively you could re-label the columns in XML.
BEGIN
SET NOCOUNT ON
Declare @MyRowVariable nvarchar(max)
Declare @MyColumnVariable nvarchar(max)
Set @MyColumnVariable = ''
Set @MyRowVariable = ''
Set @MyColumnVariable = @MyColumnVariable+'<column><name>County</name><reference>True</reference><display></display></column><column><name>Reps</name><reference>False</reference><display></display></column>'
Set @MyRowVariable = @MyRowVariable+(Select county as County, count(id) as Reps from TrainingData group by county
for xml path('row'))
END
Select '<xml><columns>' + @MyColumnVariable + '</columns><rows>' + @MyRowVariable + '</rows></xml>' as anythingyoulike
