7
\$\begingroup\$

I need to compare two XML columns. I currently have about 1000 rows of data that this is running against and it takes about 20min. Is there anyway I can improve this query to run faster. 

SELECT
  MAX(T.CaseName) AS CaseName,
  T.PartNumber,
  T.NodeName,
  T.OldValue,
  T.NewValue
FROM (SELECT
  C.CaseName,
  old.N.value('../../../MaterialName[1]', 'nvarchar(100)') AS PartNumber,
  old.N.value('Name[1]', 'nvarchar(4000)') AS NodeName,
  old.N.value('Value[1]', 'nvarchar(4000)') AS OldValue,
  new.N.value('Value[1]', 'nvarchar(4000)') AS NewValue
FROM Cases c
CROSS APPLY BomDataCase.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial/BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') AS old (N)
CROSS APPLY BomDataChange.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial/BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') AS new (N)
INNER JOIN LibraryStatuses ls
  ON c.[Status] = ls.StatusId
  AND c.LibraryId = ls.LibraryId
WHERE old.N.value('../../../MaterialName[1]', 'nvarchar(100)') = new.N.value('../../../MaterialName[1]', 'nvarchar(100)')
AND old.n.value('Name[1]', 'nvarchar(100)') = new.n.value('Name[1]', 'nvarchar(100)')
AND old.n.value('Value[1]', 'nvarchar(100)') <> new.n.value('Value[1]', 'nvarchar(100)')
AND ls.name = 'Review') AS T
GROUP BY T.PartNumber,
         T.NodeName,
         T.OldValue,
         T.NewValue

Each xml string is fairly large about 1000 to 1500 lines.

This is the Cases table structure. 

CREATE TABLE [dbo].[Cases](
    [CaseId] [int] IDENTITY(1,1) NOT NULL,
    [LibraryId] [int] NOT NULL,
    [CaseName] [nvarchar](500) NULL,
    [ConfigId] [nvarchar](50) NOT NULL,
    [CurrentConfigId] [nvarchar](50) NULL,
    [PartNumber] [nvarchar](50) NOT NULL,
    [Image] [nvarchar](250) NULL,
    [Status] [int] NULL,
    [Price] [decimal](18, 0) NULL,
    [Comments] [nvarchar](500) NULL,
    [Error] [nvarchar](max) NULL,
    [LastRun] [datetime] NULL,
    [LastRunApplication] [nvarchar](100) NULL,
    [BomDataCase] [xml] NULL,
    [BomDataChange] [xml] NULL,
 CONSTRAINT [PK_Cases] PRIMARY KEY CLUSTERED 
(
    [CaseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Example XML String.

<TopBomComponents>
  <TopBomComponents>
    <TopBomComponent>
      <ComponentName>TopComponent</ComponentName>
      <BomMaterialAttributes>
        <CustomBomMaterialAttributes>
          <CustomBomMaterialAttribute>
            <Name>ATTACH SUB BOM</Name>
            <Value>True</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>ATTACH SUB ROUTE</Name>
            <Value>True</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>BOM CONSUMPTION TYPE</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>CALCULATION</Name>
            <Value>1</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>COLOR DIMENSION</Name>
            <Value>_</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Color_Description</Name>
            <Value>_</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Color_LongDesc</Name>
            <Value>_</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Config_LongDesc</Name>
            <Value>This is just a bunch of text that will keep going and going.
</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>CONFIGURATION DIMENSION</Name>
            <Value>32942</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Configuration_Description</Name>
            <Value>More text to talk about the product.
</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>CONSUME ON OPERATION END</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>FLUSHING PRINCIPLE</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Height</Name>
            <Value>1.75</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Length</Name>
            <Value>83.38</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>MATERIAL TYPE</Name>
            <Value>2</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>PART NUMBER</Name>
            <Value>TopMaterialNumber</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>PER SERIES</Name>
            <Value>1</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>QUANTITY</Name>
            <Value>1</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>ROUNDING UP</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>ROUNDING UP MULTIPLES</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>SCRAP FACTOR</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>SITE</Name>
            <Value>1123</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>SIZE DIMENSION</Name>
            <Value>35.750Wx83.375Lx1.750T</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Size_Description</Name>
            <Value>35.750Wx83.375Lx1.750T</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Size_LongDesc</Name>
            <Value>35.750Wx83.375Lx1.750T</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>STYLE DIMENSION</Name>
            <Value>Bir Nat-PS-AA Grd-BB</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Style_Description</Name>
            <Value>Bir Nat-PS-AA Grd-BB</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Style_LongDesc</Name>
            <Value>Bir Nat-PS-AA Grd-BB</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>UOM</Name>
            <Value>ea</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>VARIABLE SCRAP</Name>
            <Value>0</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>WAREHOUSE</Name>
            <Value>1123-100</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Weight</Name>
            <Value>100.54</Value>
          </CustomBomMaterialAttribute>
          <CustomBomMaterialAttribute>
            <Name>Width</Name>
            <Value>35.75</Value>
          </CustomBomMaterialAttribute>
        </CustomBomMaterialAttributes>
      </BomMaterialAttributes>
      <BomMaterials>
        <BomMaterial>
          <MaterialName>Material1</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>AbcdData1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>0.12</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>lb</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material2</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>Abcdata2</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>1.438Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>1.438Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>1.438Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>FSCMix</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>FSCMix</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>FSCMix</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material3</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>33.500Wx81.125Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>33.500Wx81.125Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>33.500Wx81.125Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>CW-20min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>CW-20min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>Controlled Wood-20min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material4</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>Lite</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Lite</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>Lite</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata4</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>2</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>36.500Wx85.000Lx0.063T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>36.500Wx85.000Lx0.063T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>36.500Wx85.000Lx0.063T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>CARBP2-FSCCW-90min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>CARBP2-FSCCW-90min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>CARBP2-FSCCW-90min</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material5</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>Bir Nat-PS-AA Grd</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Bir Nat-PS-AA Grd</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>Bir Nat-PS-AA Grd</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>FSC Controlled Wood</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>513</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>FSC Controlled Wood</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COST</Name>
                <Value>13.92</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Height</Name>
                <Value>0.02</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Length</Name>
                <Value>86</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata5</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PRICETYPE</Name>
                <Value>Cost</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>2</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>37.000Wx86.000Lx0.020T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>37.000Wx86.000Lx0.020T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>37.000Wx86.000Lx0.020T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>BB</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>BB</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>BB</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Weight</Name>
                <Value>1.89</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Width</Name>
                <Value>37.0</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material6</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata6</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>7</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>lf</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material7</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata7</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>1.98</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>lb</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material8</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>AN 971-2062-H5PRS</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Sealer</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata8</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>135.92</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>g</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material9</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>Mapl Nat Split</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Mapl Nat Split</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>Mapl Nat Split</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata9</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>2</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>1.281Wx84.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>1.281Wx84.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>1.281Wx84.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material 10</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>AN 971-2062-H5PRS</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Stile Sealer</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata10</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>4.03</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>g</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material 11</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>AN 971-2063-H5PRS</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Stile UV Topcoat</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>abcdata11</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>5.15</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>g</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material12</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>Mapl</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>Mapl</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_LongDesc</Name>
                <Value>Mapl</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Config_LongDesc</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONFIGURATION DIMENSION</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Configuration_Description</Name>
                <Value>_</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>ABCdata12</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SIZE DIMENSION</Name>
                <Value>1.281Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_Description</Name>
                <Value>1.281Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Size_LongDesc</Name>
                <Value>1.281Wx33.500Lx1.655T</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>STYLE DIMENSION</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_Description</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Style_LongDesc</Name>
                <Value>FSCCW</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>ea</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
        <BomMaterial>
          <MaterialName>Material13</MaterialName>
          <BomMaterialAttributes>
            <CustomBomMaterialAttributes>
              <CustomBomMaterialAttribute>
                <Name>BOM CONSUMPTION TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CALCULATION</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>COLOR DIMENSION</Name>
                <Value>AN 971-45L-5W-1323</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>Color_Description</Name>
                <Value>abc</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>CONSUME ON OPERATION END</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>FLUSHING PRINCIPLE</Name>
                <Value>3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>MATERIAL TYPE</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PART NUMBER</Name>
                <Value>ABCData</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>PER SERIES</Name>
                <Value>1</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>QUANTITY</Name>
                <Value>77.3</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>ROUNDING UP MULTIPLES</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SCRAP FACTOR</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>SITE</Name>
                <Value>1123</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>UOM</Name>
                <Value>g</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>VARIABLE SCRAP</Name>
                <Value>0</Value>
              </CustomBomMaterialAttribute>
              <CustomBomMaterialAttribute>
                <Name>WAREHOUSE</Name>
                <Value>1123-100</Value>
              </CustomBomMaterialAttribute>
            </CustomBomMaterialAttributes>
          </BomMaterialAttributes>
        </BomMaterial>
      </BomMaterials>
      <BomComponents />
    </TopBomComponent>
  </TopBomComponents>
</TopBomComponents>
| improve this question | |
\$\endgroup\$
9
+100
\$\begingroup\$

Try this query instead on your data and tell how it performs. Not sure it will return exactly the same as your query but it should be close enough so you can adjust to what you want.

select C.CaseName,
       O.PartNumber,
       O.NodeName,
       O.Value as OldValue,
       N.Value as NewValue
from dbo.Cases as C
  cross apply (
              select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                     A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                     A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
              from C.BomDataCase.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
              ) as O
  cross apply (
              select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                     A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                     A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
              from C.BomDataChange.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
              ) as N
where O.PartNumber = N.PartNumber and
      O.NodeName = N.NodeName and
      O.Value <> N.Value;

The parts that should make a difference on performance is that I have replaced the use of the parent axis ../ with an extra call to cross apply nodes() and I specify the text() node in the values() function.

Update:

Using 1000 rows and the XML provided in the question for both XML columns I timed the above query to about 4 minutes on my machine. The execution plan has two branches responsible for shredding each of the two XML columns. The bad thing with this plan is that the second branch is executed once for each row coming out of the first branch. It would be much better if the shredding of the columns could be done independently and then joined together.

So I tried this rewrite of the query.

select C.CaseName,
       T.PartNumber,
       T.NodeName,
       T.OldValue,
       T.NewValue
from dbo.Cases as C
  cross apply (
              select O.PartNumber,
                     O.NodeName,
                     O.Value as OldValue,
                     N.Value as NewValue
              from (
                   select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                          A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                          A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
                   from C.BomDataCase.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                     cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
                   ) as O
                inner join (
                           select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                                  A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                                  A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
                           from C.BomDataChange.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                             cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
                           ) as N
                  on O.PartNumber = N.PartNumber and
                     O.NodeName = N.NodeName
              where O.Value <> N.Value
              ) as T;

And it worked quite good. Execution time dropped to 62 seconds. The shredding of the XML is done only once per XML column and the result of the second branch is stored in a table spool and that spool is used for all rows coming out of the first branch.

How about if we could make the branches join together with a merge join instead. The input to a merge join must be sorted so we would expect to see two extra sorts in the execution plan.

Same query as above with a merge join hint.

select C.CaseName,
       T.PartNumber,
       T.NodeName,
       T.OldValue,
       T.NewValue
from dbo.Cases as C
  cross apply (
              select O.PartNumber,
                     O.NodeName,
                     O.Value as OldValue,
                     N.Value as NewValue
              from (
                   select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                          A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                          A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
                   from C.BomDataCase.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                     cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
                   ) as O
                inner merge join (
                           select M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
                                  A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
                                  A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
                           from C.BomDataChange.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
                             cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X)
                           ) as N
                  on O.PartNumber = N.PartNumber and
                     O.NodeName = N.NodeName
              where O.Value <> N.Value
              ) as T;

And it worked as expected. The branches are sorted, merge join is used and execution time dropped again. This time to 23 seconds.

Changing the join to a hash join was even better inner hash join, 13 seconds.

Another way to make this go faster is to shred the XML from each column to two temp tables and then join the temp tables to see what is different.

select C.CaseId,
       C.CaseName,
       M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
       A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
       A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
into #Old
from dbo.Cases as C
  cross apply C.BomDataCase.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
    cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X);

select C.CaseId,
       C.CaseName,
       M.X.value('(MaterialName/text())[1]', 'nvarchar(4000)') as PartNumber,
       A.X.value('(Name/text())[1]', 'nvarchar(4000)') as NodeName,
       A.X.value('(Value/text())[1]', 'nvarchar(4000)') as Value
into #New
from dbo.Cases as C
  cross apply C.BomDataChange.nodes('/TopBomComponents/TopBomComponents/TopBomComponent/BomMaterials/BomMaterial') as M(X)
    cross apply M.X.nodes('BomMaterialAttributes/CustomBomMaterialAttributes/CustomBomMaterialAttribute') as A(X);

create clustered index IX_O on #Old(CaseId, PartNumber, NodeName);
create clustered index IX_N on #New(CaseId, PartNumber, NodeName);

select O.CaseName,
       O.PartNumber,
       O.NodeName,
       O.Value as OldValue,
       N.Value as OldValue
from #Old as O
  inner join #New as N
    on O.CaseId = N.CaseId and
       O.PartNumber = N.PartNumber and
       O.NodeName = N.NodeName
where O.Value <> N.Value;

drop table #Old;
drop table #New;

For me this was the fastest way of doing the comparison with an execution time of 7 seconds.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I just ran both side by side and my original ran in 28 min and yours ran in 15. So this did cut it in half. Thank you so much for that. I might just have to live with that. I would prefer much faster but i don't know if we will do better than half. \$\endgroup\$ – pricejt Sep 19 '16 at 17:20
  • \$\begingroup\$ @pricejet how many bommaterial nodes do you have on average in each row and how many attributes per bommaterial node, on average. \$\endgroup\$ – Mikael Eriksson Sep 19 '16 at 18:51
  • \$\begingroup\$ about 13 BomMaterials and about 15 attributes each is the norm. There are a few that are double that. I have also included in the post an example of the xml with a few values replaced. \$\endgroup\$ – pricejt Sep 19 '16 at 19:54
  • 3
    \$\begingroup\$ @MikaelEriksson wow this is incredible how much you were able to improve the performance of this query, I'm definitively bookmarking this for the future! \$\endgroup\$ – Phrancis Sep 20 '16 at 12:25
  • 1
    \$\begingroup\$ @MikaelEriksson looks like this ran on my real data in 16 seconds. Thank you so much. This is really impressive. I did find an issue with my logic though (not related to this performance stuff) Found out I am not showing differences if one xml has more parts than the other. I am also not comparing the top level component only its children. I will keep working through it hopefully that doesn't add too much to the performance. Thanks again. \$\endgroup\$ – pricejt Sep 20 '16 at 14:32
5
\$\begingroup\$

Nitpicks

Here are a few small critiques to start with:

  • Single-letter table reference aliases are often discouraged because they do not convey any meaning, such that you have to go back to their sources to understand what they actually reference to. For example, your sub-query aliased as T would make more sense with an alias like CaseChanges.

  • SQL Server/Transact-SQL allows structures called Common Table Expressions; they work just like nested sub-queries but often make the code easier to read. For example:

    --Nested subquery:
SELECT * FROM (
    SELECT Foo, Bar FROM MyTable
) AS MySubset
--Common table expression:
WITH MySubset AS (
    SELECT Foo, Bar FROM MyTable
)
SELECT * FROM MySubset

  • You can reduce the need for horizontal scrolling and thus increase the readability of your nodes() method calls by adding line breaks inside your path string (those will be ignored by the SQL query engine), for instance:

        CROSS APPLY BomDataCase.nodes(
            '/TopBomComponents/TopBomComponents/TopBomComponent
            /BomMaterials/BomMaterial/BomMaterialAttributes
            /CustomBomMaterialAttributes/CustomBomMaterialAttribute'
        ) AS old(N)

The problem with XML data...

There is a fundamental and unavoidable conflict when it comes to using XML data type inside of SQL:

  1. SQL represents data as row sets;

  2. XML represents data as hierarchies;

  3. Therefore, all data stored as XML must be transformed to a row set prior to being used inside of SQL queries.

That is where the nodes() method is needed, and also where your performance bottleneck is, as I'm sure you have noticed. Here is a very zoomed out version of the execution plan, for reference:

exec-plan

All of the places where it branches down from the root execution path are nested loops (12 of them), and each branch itself contains multiple other nested loops. Doing this on 1000 rows of XML data, each containing 1000-1500 nodes, twice (new and old), is why this takes so long to execute.

Possible database-level solutions

Mikael Eriksson suggested some extremely powerful performance improvements, so this is mostly for reference (as I wrote this prior to his incredible answer).

If this query's execution speed is important to your business needs, there could be some solutions at the database level, in addition to the query level. I don't know what your database is being used for or what its volume of writes (i.e. INSERT, UPDATE and DELETE operations) to the Cases table is, so I will propose a few ideas that you could consider.

If the volume of writes is pretty low, consider adding a trigger on inserts, and possibly on updates to the XML fields in question, that will parse out the fields you need from the XML data at that time and put the data into another table created for that purpose.

This would "spread out" the cost of processing the XML data as rows are added or modified, and make your query practically instantaneous. I would not recommend this solution if the Cases table has a lot of write operations happening frequently, as triggers can degrade the performance of those write operations significantly due to locks being held until the trigger is completed.

References:

If the volume of writes is pretty high, or for any other reason that using triggers would be an issue, you could have whatever outside process/application that is inserting this data into the SQL database parse out some of the data from the XML and also pass those data values to the database.

From there, you could as mentioned above put the values into a table created for that purpose to greatly speed up querying this. The primary hurdle with this approach is that modifying something outside of the database would be needed. On the other hand, this would most likely be the best performance of any of the approaches, as you remove the responsibility of parsing XML from the database and give it to something that is better equipped to do that.

How to make this change would depend entirely on how the database is interacted with by outside processes/applications, so I cannot offer any specific advice.

You could possibly also try an indexed view (but not a regular/non-indexed view) which has this XML data parsed for you in advance. The performance gains from this approach could vary greatly depending on many factors, so I will link you to this great article on simple-talk.com for reference:

SQL Server Indexed Views: The Basics

Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression. They aren't there for reasons of performance, and so indexed views are designed to remedy this shortcoming. They're great in certain circumstances but they represent a trade-off, and they come with considerable "small print".

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.