==== 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|code format"sql"]]

–

– Author: Team Cyrious

– Create date: 5/2010

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

–

CREATE View cv_ProductUDFByRow

AS

<code>
  SELECT
</code>

<code>
    ID,
</code>

<code>
    ThisUDF.query('(local-name(.))') AS VariableName,
</code>

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

<code>
  FROM
</code>

<code>
  (
</code>

<code>
    SELECT ID,
</code>

<code>
           Cast(UDFXML as XML) as XMLField
</code>

<code>
    FROM CustomerGoodsItem
</code>

<code>
    WHERE (ClassTypeID  12000) and (UDFXML is not NULL)
</code>

<code>
  ) Temp
  -- Cross Join this with a row for each ID in the UDF
</code>

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

''Version Information ''

   * ''Entered : 5/2010''
  * ''Version : 4.5''
''Related SQLs ''

''''

\\