{$page}

In several places (most notably Part and Product UDFs), Control stores the data in XML format inside of text fields. The primary reason to do this is to consolidate multiple records into one read/write for the database engine, thereby improving performance.

However, this can make writing SQL queries (such as used in reporting) rather difficult. This article discusses how to use the XML functions in SQL 2005 and 2008 to pull this information out. It is not meant to be a full SQL tutorial, but just an extra help for those already knowledgeable in the ways of SQL.

Concept

The basic concept we will use is:

• Specify an XPath to the node you want to extract (e.g., Parts/Part/ID). * Extract these rows * Cross-Join them with the same table Cautions - Pulling out XML data takes a lot of reconfiguring the data. Hence, it is wise to minimize the data searched if practical. - It would be hard to conceive of a syntax more confusing than that chosen by the SQL standards body for this functionality. Good luck! Example 1 - Pull All Variable Information from TransDetails.ParamStr All of the variables (formerly called parameters) associated with a line item are stored in the ParamStr field on the TransDetail table. The following query will pull out a list of these variables for a given line item along with their value: code – This command is needed for databases upgraded from / set for SQL 2000 compatibility SET NUMERIC_ROUNDABORT OFF; SELECT ID as TransDetailID, – if returning the text() value, we must tell it the data type ThisVariable.value('(VariableID/text())[1]', 'varchar(15)') as VariableID, ThisVariable.value('(VariableName/text())[1]', 'varchar(25)') as Name, ThisVariable.value('(ValueAsString/text())[1]', 'varchar(255)') as Value, – when returning the node() it will be an XML datatype ThisVariable.query('(node())') as VariableNode FROM ( SELECT ID, Cast(TransDetail.ParameterStr as XML) as XMLField FROM TransDetail WHERE ParameterStr is not NULL ) Temp – Cross Join this with a row for each ID in the Variable CROSS APPLY XMLField.nodes('Parameters/Parameter') as Variables(ThisVariable)

code

The resulting table looks like:

Example 2 - Pull One Variables Information from TransDetails.ParamStr
If you only want one variable, you can search directly inside the XML by diving down the “path” and filtering on the variable name. This example pulls the PrintedArea variable only:
Use this code if your TransDetail.ParameterStr is already an XML field. This will be the case for most customers who started with Control in 2014 or later.
code
– This command is needed for databases upgraded from / set for SQL 2000 compatibility
SET NUMERIC_ROUNDABORT OFF;
SELECT
ID as TransDetailID,
– don't forget to set the returned data type as the second parameter
ParameterStr.value('(/Parameters/Parameter[VariableName“PrintedArea”]/ValueAsString)[1]', 'decimal(18,4)') as TheValue
FROM TransDetail
WHERE ParameterStr is not NULL
AND TransHeaderID dbo.OrderIDFromNumber(1234) – Replace 1234 with the Order Number you are looking for
code
Use this code if your TransDetail.ParameterStr is a string or text field and not an XML field. This will be the case for most customers who started with Control before 2014 and have not changed it over.
code
– This command is needed for databases upgraded from / set for SQL 2000 compatibility
SET NUMERIC_ROUNDABORT OFF;
SELECT
ID as TransDetailID,
– don't forget to set the returned data type as the second parameter
XMLField.value('(/Parameters/Parameter[VariableName“PrintedArea”]/ValueAsString)[1]', 'decimal(18,4)') as TheValue
FROM
(
– If the ParameterStr is a VarChar or Text, we must cast it to XML.
– If it is already XML, you can pull it straight from the TransDetail table.
SELECT ID,
Cast(TransDetail.ParameterStr as XML) as XMLField
FROM TransDetail
WHERE ParameterStr is not NULL
) Temp
code
The resulting table looks like:
||~ TransDetailID ||~ TheValue ||

Example 3 - Pull Multiple Variables Information from TransDetails.ParamStr into a Single Row
If you want multiple variables in a single row, you can pull each Variables value as individual fields. This example pulls the description variable only:
code
– This command is needed for databases upgraded from / set for SQL 2000 compatibility
SET NUMERIC_ROUNDABORT OFF;
SELECT
ID as TransDetailID,
– if returning the text() value, we must tell it the data type
XMLField as AllVariables,
XMLField.query('(node()[VariableName“Area”])') as TheAreaNode,
XMLField.value('(node()[VariableName“Area”]/ValueAsString/text())[1]', 'varchar(15)') as TheArea,
XMLField.query('(node()[VariableName“Height”])') as TheHeightNode,
XMLField.value('(node()[VariableName“Height”]/ValueAsString/text())[1]', 'varchar(15)') as TheHeight,
XMLField.query('(node()[VariableName“Width”])') as TheWidthNode,
XMLField.value('(node()[VariableName“Width”]/ValueAsString/text())[1]', 'varchar(15)') as TheWidth
FROM
(
SELECT ID,
Cast(TransDetail.ParameterStr as XML) as XMLField
FROM TransDetail
WHERE ParameterStr is not NULL
) Temp
code
The resulting table will return:
||~ Field Name ||~ Type ||~ Description ||

Example 4 - Return Selected UDFs

This query returns the value of specific UDFs from the UDF table as columns in a table. You could easily join them on the underlying table to create the extentions you need.

code

– This command is needed for databases upgraded from / set for SQL 2000 compatibility

SET NUMERIC_ROUNDABORT OFF;

SELECT top 50

ID as PartID,

1. - if returning the text() value, we must tell it the data type

ThisUDF.value('(Routing___Simple_Cut_Perimeter_Multiplier/text())[1]', 'varchar(15)') as Routing___Simple_Cut_Perimeter_Multiplier,

ThisUDF.value('(Routing___Standard_Cut_Perimeter_Multiplier/text())[1]', 'varchar(15)') as Routing___Standard_Cut_Perimeter_Multiplier,

ThisUDF.value('(Routing___Complex_Cut_Perimeter_Multiplier/text())[1]', 'varchar(15)') as Routing___Complex_Cut_Perimeter_Multiplier,

ThisUDF.value('(Routing___Cut_Speed/text())[1]', 'varchar(15)') as Routing___Cut_Speed,

1. - returns all UDFs as an XML datatype (with no root node in this case)

ThisUDF.query('(node())') as VariableNode,

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) code Example 5 - Extract All UDF Values This example extracts all of the UDF values defined for a Part. (Note that if the UDF is empty, it may not show up in the UDF list so you have to treat a lack of a row as NULL for that value.) The value is listed as both an XML and VarChar(25) value, though you should probably chose one and make it the correct size. code – This command is needed for databases upgraded from / set for SQL 2000 compatibility SET NUMERIC_ROUNDABORT OFF; SELECT ID as PartID, – returns all UDFs as an XML datatype (with no root node in this case) ThisUDF.query('(.)') as VariableNode, ThisUDF.query('(local-name(.))') as VariableName, ThisUDF.query('(text())[1]') as VariableValueXML, ThisUDF.value('(text())[1]', 'varchar(25)') as VariableValueStr 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)

code

Example 6 - Extract One UDF

This example shows how to find a single UDF value for a Part. (Note that if the UDF is empty, it may return NULL for the entire row.) The value is listed as a VarChar(25) value, though you should probably chose one and make it the correct size.

code

declare @UDFName varchar(128);

set @UDFName 'Routing___Complex_Cut_Perimeter_Multiplier';

– This command is needed for databases upgraded from / set for SQL 2000 compatibility

SET NUMERIC_ROUNDABORT OFF;

SELECT top 50

ID as PartID,

ThisUDF.value('(text())[1]', 'varchar(25)') 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/*[local-name() sql:variable(“@UDFName”)]') as PartUDFs(ThisUDF) code Example 7 - Pulling Variable Information from a Line Item This example shows you how to pull a list of line items with the value of the Quantity variable and the InternalNotes variable. code_formatsql SELECT TD.ID as TransDetailID , TD.GoodsItemCode , ThisVariable.value('(node()[VariableName“Quantity”]/ValueAsString/text())[1]', 'varchar(50)') as [Quantity] , ThisVariable.value('(node()[VariableName“InternalNotes”]/ValueAsString/text())[1]', 'varchar(max)') as [InternalNotes] FROM TransDetail TD WITH(NOLOCK) LEFT JOIN ( SELECT TD.ID , CAST(TD.ParameterStr AS XML) AS VariableXMLField FROM TransDetail TD WITH(NOLOCK) ) T ON T.ID TD.ID CROSS APPLY VariableXMLField.nodes('Parameters') AS VARIABLES(ThisVariable)

WHERE TD.ID 10000 – Change this where statement to fit your needs

ORDER BY TD.ID

code

Using these Examples in SQL

If we combine these SQL queries with other queries, it's possible to create many other reports that incorporate the UDF's in them as well. Basically, the UDF SQL gets wrapped in parenthesis to create it's own temp table, and we can then join this table to any other SQL to allow the UDF's to be used. To join the tables properly, they must be joined on the Part.ID TempTable.ID. Also, the values in the temp table need to be added to the select statement with a line similar to temptable.*, to make sure those values are available for use.

Below is an example that uses the Part table in a main report.

code

SELECT

Part.ItemName,

Part.Description,

UDFTable.*

FROM

Part with(nolock)

left join PricingElement as PartCategory with(nolock) on PartCategory.IDPart.CategoryID

left join Station with(nolock) on Station.ID  Part.StationID

left join

(

• Above XML Statement's with UDF's you need listed here*

) as UDFTable ON Part.ID UDFTable.ID

WHERE Part.ID is not null and Part.ID > 0

code

Converting Checkbox Parameters Values

Checkbox Parameters are stored as Yes/No values in the ValueAsString node inside the parameters. It is often desirable to retrieve the value as a BIT field, rather than a varchar field. One easy approach to do this is to insert a custom function to convert the string value to a bit value. The code to insert this is found below.

code

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

–

– Author: Cyrious Software

– Create date: 2014-02

– Description: Converts a text string to a bit

–

CREATE FUNCTION StringToBit

(

  @string char(1)

)

RETURNS bit

AS

BEGIN

  DECLARE @Result bit  NULL;

Declare @Ch char  upper(substring( ltrim(@string), 1, 1));

if (@Ch in ('Y', 'y', '1', '2', '3', '4', '5', '6', '7', '8', '9') )

  SET @Result  1

else if (@Ch in ('N', 'n', '0') )

  SET @Result  0 ;

  RETURN @Result

END

GO

code

Once this is done, converting the value is done easily as in this example:

code

  SELECT

    ID as TransDetailID

  , TransHeaderID

  , dbo.StringToBit( ParameterStr.value('(/Parameters/Parameter[VariableName"RUSH"]/ValueAsString/text())[1]', 'char(3)') ) as IsRush

  FROM TransDetail TD

  WHERE TD.ID  1234

code

Source

Contributor: Cyrious Software

Date: 5/2010

Version: Control 4.5

See Also

• Backlinks include_pagepage_componentbacklinks