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

WARNING

This view will only work in SQL 2005 and above. SQL 2000 does not support XML data manipulation and anyone needing these features must upgrade. All support for SQL 2000 will be end with Control 5.0.

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 Part UDFs

–

CREATE View cv_PartUDFByRow

AS

SELECT

  ID,

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

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

FROM

(

    SELECT ID, Cast(UDFXML as XML) as XMLField

  FROM Part

  WHERE UDFXML is not NULL

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

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

;

Version Information

• Entered : 5/2010
• Version : 4.5

Related SQLs