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: – 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)

The resulting table looks like:

~ TransDetailID~ VariableID~ Name~ Value~ VariableNode
1001(2053,14505)ProductionNote
1001(2170,14505)Originals1
1001(2169,14505)Sets1
1001(4755,14505)PrintSalesNotesWorkOrderNo
1001(4754,14505)PrintSalesNotesInvoiceNo
1001475314505SalesNotes
1001(130,14505)TicketType
1001(120,14505)Priority
1001(111,14505)InternalNotes
1001(110,14505)DescriptionSales Notes
1001(1,14505)Quantity1

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.

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

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.

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

The resulting table looks like:

TransDetailIDTheValue
1001<variableid>
1002<variableid>
1003<variableid>



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:

– 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

The resulting table will return:

Field NameTypeDescription
TransDetailIDINT
AllVariablesXMLFieldThe XML of all the variables for the TransDetail
TheAreaNodeXMLFieldThe XML of the area variable for the TransDetail
TheAreavarchar(15)The value of the area variable for the TransDetail
TheHeightNodeXMLFieldThe XML of the height variable for the TransDetail
TheHeightvarchar(15)The value of the height variable for the TransDetail
TheWidthNodeXMLFieldThe XML of the width variable for the TransDetail
TheWidthvarchar(15)The value of the width variable for the TransDetail

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.

-- This command is needed for databases upgraded from / set for SQL 2000 compatibility
SET NUMERIC_ROUNDABORT OFF;
SELECT top 50
  ID as PartID,
  -- 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,
  -- 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)

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.

-- 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)

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.

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)

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 format"sql"

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 //

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.

// 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 //

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.

// 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 //

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

// 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 //

Source Contributor: Cyrious Software Date: 5/2010 Version: Control 4.5


You could leave a comment if you were logged in.