passing column name through XML

[chris123321]

The XML has a column that is being passed as a parameter and that column needs to be part of the last select statement.

So in the example below, UnitPrice is a value in ProductList. @SortOrder is set to UnitPrice and the values of UnitPrice need to queried in the last select statement. The problem is that @SortOrder shows 'UnitPrice' in the last selectment instead of the values of UnitPrice. What modification do I need to do to the script below to show the values of UnitPrice?

DECLARE @InputXML             nvarchar(1000),
        @XMLHandle            int,
        @SQL                  nvarchar(1000),
        @Update               datetime,
        @ProductFields          nvarchar(100)

DECLARE @ProductList         TABLE(ProductFields nvarchar(200))
CREATE TABLE #ProductTable        
(
    ProductId int
)

SET @InputXML = '<NewDataSet>
                 <ProductList ProductFields="UnitPrice" />
                 </NewDataSet>'

EXEC sp_xml_preparedocument @XMLHandle OUTPUT, @InputXML


SET @Update = '04/02/2007'

IF EXISTS(SELECT * FROM OPENXML(@XMLHandle, '/NewDataSet/ProductList', 1))
BEGIN
    INSERT @ProductList(ProductFields)
        SELECT ProductFields
        FROM OPENXML(@XMLHandle, '/NewDataSet/ProductList', 1)
        WITH (ProductFields        nvarchar(100)    '@ProductFields')
END
ELSE
BEGIN
    SET @ProductFields = -1
END    

DECLARE @SortOrder nvarchar(100)

IF EXISTS(Select ProductFields FROM @ProductList)
BEGIN
    
    SELECT @SortOrder = ProductFields
    FROM @ProductList
END

--SELECT @SortOrder

SELECT @SQL = ' SELECT TOP 10 ProductId AS [Product Number] 
                FROM Products 
                WHERE LastUpdate >= ''' + CAST(@Update AS nvarchar(30)) + '''' 

INSERT #ProductTable

--PRINT @SQL
EXEC(@SQL)

SELECT ProductId,
    @SortOrder
FROM #ProductTable

DROP TABLE #ProductTable

Thanks.

 

[chris123321]

So the solution was to do another EXEC(@SQL)..

DECLARE @InputXML             nvarchar(1000),
        @XMLHandle            int,
        @SQL                  nvarchar(1000),
        @Update               datetime,
        @ProductFields          nvarchar(100)

DECLARE @ProductList         TABLE(ProductFields nvarchar(200))
CREATE TABLE #ProductTable        
(
    ProductId int
)

CREATE TABLE #ProductTest
(
   ProductId int,
   UnitPrice money
)


SET @InputXML = '<NewDataSet>
                 <ProductList ProductFields="UnitPrice" />
                 </NewDataSet>'

EXEC sp_xml_preparedocument @XMLHandle OUTPUT, @InputXML


SET @Update = '04/02/2007'

IF EXISTS(SELECT * FROM OPENXML(@XMLHandle, '/NewDataSet/ProductList', 1))
BEGIN
    INSERT @ProductList(ProductFields)
        SELECT ProductFields
        FROM OPENXML(@XMLHandle, '/NewDataSet/ProductList', 1)
        WITH (ProductFields        nvarchar(100)    '@ProductFields')
END
ELSE
BEGIN
    SET @ProductFields = -1
END    

DECLARE @SortOrder nvarchar(100)

IF EXISTS(Select ProductFields FROM @ProductList)
BEGIN
    
    SELECT @SortOrder = ProductFields
    FROM @ProductList
END

--SELECT @SortOrder

SELECT @SQL = ' SELECT TOP 10 ProductId AS [Product Number] 
                FROM Products 
                WHERE LastUpdate >= ''' + CAST(@Update AS nvarchar(30)) + '''' 

INSERT #ProductTable

--PRINT @SQL
EXEC(@SQL)

SELECT @SQL = 'SELECT ProductId, ' + 
    @SortOrder + '
FROM #ProductTable

INSERT #ProductTest

EXEC(@SQL)

SELECT *
FROM #ProductTest

DROP TABLE #ProductTable
DROP TABLE #ProductTest

Just one more question..

When I do the 2nd SELECT(@SQL), how come the @SQL string isn't concatenated with the 1st SELECT(@SQL)?

So how come the @SQL doesn't read 'SELECT TOP 10...SELECT ProductId'?

Thanks.

 

[gmmastros]

In order to concatenate strings, you have to add the variable (which you are not doing).

ex:

Declare @SQL VarChar(8000)

Set @SQL = 'S'
Set @SQL = @SQL + 'e'
Set @SQL = @SQL + 'l'
Set @SQL = @SQL + 'e'
Set @SQL = @SQL + 'c'
Set @SQL = @SQL + 't'

Select @SQL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[chris123321]

thanks George..