{$page} 



Explanation of View 



This view provides a list of all of the UDFs for a product, with each UDF value being on a separate row.



Sample Data 



This View produces a result set with these columns:



||~ ID ||~ VariableName ||~ UDFValue ||
|| 1002 || CommissionRate || 10 ||
|| 1002 || RoyaltyRateOnProduct || 0 ||
|||||| ... ||
|| 1003 || CommissionRate || 20 ||
|| 1003 || RoyaltyRateOnProduct || 10 ||
|||||| ... ||
SQL 



[[code_formatsql]]



-- 



-- Author:		Team Cyrious



-- Create date: 5/2010



-- Description:	This function returns a table containing rows of the Product UDFs



-- 



CREATE View cv_ProductUDFByRow



AS



    SELECT



      ID, 



      ThisUDF.query('(local-name(.))') AS VariableName,



      cast(ThisUDF.value('(text())[1]', 'varchar(255)') as Varchar(255)) as UDFValue



        



    FROM 



    ( 



	    SELECT ID,



		       Cast(UDFXML as XML) as XMLField 



	    FROM CustomerGoodsItem



	    WHERE (ClassTypeID  12000) and (UDFXML is not NULL)



    ) Temp


    -- Cross Join this with a row for each ID in the UDF



    CROSS APPLY XMLField.nodes('//UDFs/*') as ProductUDFs(ThisUDF)



[[code]]



Version Information 
  * Entered : 5/2010
  * Version : 4.5



Related SQLs 
  * Backlinks [[include_pagepage_componentbacklinks]]