==== Explanation of View ====

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:

|~ ID||~ VariableName||~ UDFValue||
|1002||Routing_Simple_Cut_Perimeter_Multiplier||1||
|1002||Routing_Standard_Cut_Perimeter_Multiplier||1.5||
|1002||Routing_Complex_Cut_Perimeter_Multiplier||3||
|…|| | | | |
|1003||Routing_Simple_Cut_Perimeter_Multiplier||1||
|1003||Routing_Standard_Cut_Perimeter_Multiplier||1.5||
|1003||Routing_Complex_Cut_Perimeter_Multiplier||3||
|…|| | | | |

**SQL**

[[:code_formatsql|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

<code>
SELECT
</code>

<code>
  ID,
</code>

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

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

<code>
FROM
</code>

<code>
(
</code>

<code>
    SELECT ID, Cast(UDFXML as XML) as XMLField
</code>

<code>
  FROM Part
</code>

<code>
  WHERE 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 PartUDFs(ThisUDF)
</code>

;

''Version Information ''

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

\\