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) | Originals | 1 | … | |||||
1001 | (2169,14505) | Sets | 1 | … | |||||
1001 | (4755,14505) | PrintSalesNotesWorkOrder | No | … | |||||
1001 | (4754,14505) | PrintSalesNotesInvoice | No | … | |||||
1001 | 475314505 | SalesNotes | … | ||||||
1001 | (130,14505) | TicketType | … | ||||||
1001 | (120,14505) | Priority | … | ||||||
1001 | (111,14505) | InternalNotes | … | ||||||
1001 | (110,14505) | Description | Sales Notes | … | |||||
1001 | (1,14505) | Quantity | 1 | … |
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:
TransDetailID | TheValue |
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 Name | Type | Description | |||
TransDetailID | INT | ||||
AllVariables | XMLField | The XML of all the variables for the TransDetail | |||
TheAreaNode | XMLField | The XML of the area variable for the TransDetail | |||
TheArea | varchar(15) | The value of the area variable for the TransDetail | |||
TheHeightNode | XMLField | The XML of the height variable for the TransDetail | |||
TheHeight | varchar(15) | The value of the height variable for the TransDetail | |||
TheWidthNode | XMLField | The XML of the width variable for the TransDetail | |||
TheWidth | varchar(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