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:

SQL

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

  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)

Version Information

• Entered : 5/2010
• Version : 4.5

Related SQLs