I have the following query:
select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero
The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.
If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.
I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.
What can I do Does anybody know if I can force the TVF to render its data somewhere so the JOIN works I was thinking a rowset function could help, but I just can't figure out how.
PLEASE HELP!!!!
Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:
CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)
RETURN
END
select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero
The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.
If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.
I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.
What can I do Does anybody know if I can force the TVF to render its data somewhere so the JOIN works I was thinking a rowset function could help, but I just can't figure out how.
PLEASE HELP!!!!
Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:
CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)
RETURN
END
INNER JOIN between a CLR-TVF and a table returns no rows
Tridex
Yes, I'm sure there are matches, and as for why, this is my scenario. I have a sales document header table (PAF) and a sales document detail (ContenidoPAF and ContenidoPAFBlob). The detail table has a XML field with detailed information about the product being sold. Since those are engineered products, the XML has information about geometry and other attributes of the components used in the product.
As you will see, there are actually two TVFs. The one that extract information from each line item of the sales document detail, and the one that get the list of items to be shipped to the customer and gets the list of elements for those items. The second TVF actually calls the first one and performs long query with unions to return everything in a single rowset. Not sure if this is the best solution.
Below the code of used in the TVFs.
// this is the TVF entry points, there are
using
System;using
System.Data;using
System.Data.SqlClient;using
System.Data.SqlTypes;using
Microsoft.SqlServer.Server;using
System.Collections;public
partial class UserDefinedFunctions{
[
SqlFunction(DataAccess = DataAccessKind.Read,FillRowMethodName =
"FillListOfSquaresForShippingLot",TableDefinition =
"Number int, Version int, Position smallint, " + "SubModel smallint, Quantity smallint, " + "SquareId nvarchar(5), " + "ParentSquareId nvarchar(5), " + "IsSash smallint, " + "IsGlazingBead smallint, " + "Width float, " + "Height float, " + "GlassNumber smallint, " + "GlassWidth float, " + "GlassHeight float")] public static IEnumerable GetListOfSquaresForShippingLot(SqlInt32 dbShippingLot){
using (SqlConnection conn = new SqlConnection("context connection=true")){
int shippingLot = -1; if (!dbShippingLot.IsNull) shippingLot = dbShippingLot.Value; string sql = "select numero, version, orden, submodel, count(*) as qty from estadosubmodelospaf " + "where shippinglotcode = " + shippingLot.ToString() + " group by numero, version, orden, submodel"; SqlCommand cmd = new SqlCommand(sql, conn);conn.Open();
sql = GetSqlForSLotSquares(shippingLot, cmd.ExecuteReader());
SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet();da.Fill(ds);
conn.Close();
return new PrefNASqlServer.SLotSquareListReader(ds);}
}
[
SqlFunction(FillRowMethodName = "FillListOfSquares",TableDefinition =
"SubModel smallint, " + "SquareId nvarchar(5), " + "ParentSquareId nvarchar(5), " + "IsSash smallint, " + "IsGlazingBead smallint, " + "Width float, " + "Height float, " + "GlassNumber smallint, " + "GlassWidth float, " + "GlassHeight float")] public static IEnumerable GetListOfSquares(SqlXml dbModel, SqlInt16 subModel){
// Put your code here return new PrefNASqlServer.SquareListReader(dbModel, subModel);}
public static void FillListOfSquares(object row, out SqlInt16 subModel, out SqlString squareId, out SqlString parentSquareId, out SqlInt16 isSash, out SqlInt16 isGlazingBead, out SqlDouble width, out SqlDouble height, out SqlInt16 glassNumber , out SqlDouble glassWidth, out SqlDouble glassHeight){
object[] rowarr = (object[])row;subModel = (
SqlInt16)(rowarr[0]);squareId = (
SqlString)(rowarr[1]);parentSquareId = (
SqlString)(rowarr[2]);isSash = (
SqlInt16)(rowarr[3]);isGlazingBead = (
SqlInt16)(rowarr[4]);width = (
SqlDouble)(rowarr[5]);height = (
SqlDouble)(rowarrglassNumber = (
SqlInt16)(rowarr[7]);glassWidth = (
SqlDouble)(rowarrglassHeight = (
SqlDouble)(rowarr[9]);}
public static void FillListOfSquaresForShippingLot(object row, out SqlInt32 number, out SqlInt32 version, out SqlInt16 position, out SqlInt16 subModel, out SqlInt16 quantity, out SqlString squareId, out SqlString parentSquareId, out SqlInt16 isSash, out SqlInt16 isGlazingBead, out SqlDouble width, out SqlDouble height, out SqlInt16 glassNumber, out SqlDouble glassWidth, out SqlDouble glassHeight){
object[] rowarr = (object[])row;number = (
SqlInt32)(rowarr[0]);version = (
SqlInt32)(rowarr[1]);position = (
SqlInt16)(rowarr[2]);subModel = (
SqlInt16 )(rowarr[3]);quantity = (
SqlInt16)(rowarr[4]);squareId = (
SqlString)(rowarr[5]);parentSquareId = (
SqlString)(rowarrisSash = (
SqlInt16)(rowarr[7]);isGlazingBead = (
SqlInt16)(rowarrwidth = (
SqlDouble)(rowarr[9]);height = (
SqlDouble)(rowarr[10]);glassNumber = (
SqlInt16)(rowarr[11]);glassWidth = (
SqlDouble)(rowarr[12]);glassHeight = (
SqlDouble)(rowarr[13]);}
private static string GetSqlForSLotSquares(int shippingLot, SqlDataReader dr){
string sql = ""; if (shippingLot != -1){
while (dr.Read()){
sql = sql + (sql ==
"" "" : "union\n") + "select " +dr[
"numero"].ToString() + " as Number, " +dr[
"version"].ToString() + " as Version, " +dr[
"orden"].ToString() + " as Position, " +dr[
"submodel"].ToString() + " as SubModel, " +dr[
"qty"].ToString() + " as Quantity, * " + "from dbo.GetListOfSquares((select [xml] from contenidopafblob where " + "numero = " + dr["numero"].ToString() + " and version = " + dr["version"].ToString() + " and orden = " + dr["orden"].ToString() + "), " + dr["submodel"].ToString() + ")\n";}
}
dr.Close();
sql = (sql !=
"" sql : "select cast(-1 as int) as Number, " + "cast(-1 as int) Version, " + "cast(-1 as smallint) as Position, " + "cast(-1 as smallint) as SubModel, " + "cast(-1 as smallint) as Quantity, " + "cast('' as nvarchar(5)) as SquareId, " + "cast('' as nvarchar(5)) as ParentSquareId, " + "cast(-1 as smallint) as IsSash, " + "cast(-1 as smallint) as IsGlazingBead, " + "cast(0 as float) as Width, " + "cast(0 as float) as Height, " + "cast(-1 as smallint) as GlassNumber, " + "cast(0 as float) as GlassWidth, " + "cast(0 as float) as GlassHeight"); return sql;}
};
// this is the helper class used called by the TVF
using
System;using
System.Collections.Generic;using
System.Text;using
System.Xml;using
System.Xml.XPath;using
System.Collections;using
System.Data;using
System.Data.SqlClient;using
System.Data.SqlTypes;namespace
PrefNASqlServer{
public class SquareListReader : IEnumerable{
short subModel; XPathDocument doc; XPathNavigator nav; public SquareListReader(SqlXml dbModel, SqlInt16 dbSubModel){
if (!dbModel.IsNull&&!dbSubModel.IsNull){
subModel = (
short)dbSubModel; XmlReader reader = dbModel.CreateReader();doc =
new XPathDocument(reader);nav = doc.CreateNavigator();
}
}
public IEnumerator GetEnumerator(){
return new SquareEnumerator(this);}
private class SquareEnumerator : IEnumerator{
XPathNodeIterator i; Object[] current; SquareListReader reader; public SquareEnumerator(SquareListReader reader){
this.reader = reader;Reset();
}
public void Reset(){
string xPath = "descendant::dsc:Square[ancestor::dsc:Hole" + "[@compositeOrder=\"" + reader.subModel.ToString() + "\"]]"; XPathExpression xPathExpr = GetXPathExpression(xPath, reader.nav.NameTable);i = reader.nav.Select(xPathExpr);
}
public bool MoveNext(){
if (i.MoveNext()){
string squareId = (string)i.Current.Evaluate("string(@id)"); short isSash = IsSash(squareId); short isGlazingBead = IsGlazingBead(squareId); short glassNumber = -1; double glassWidth = 0.0; double glassHeight = 0.0; if (isGlazingBead != 0) GetGlassInfo(squareId, ref glassNumber, ref glassWidth, ref glassHeight);current =
new Object[10];current[0] =
new SqlInt16(reader.subModel);current[1] =
new SqlString(squareId);current[2] =
new SqlString((string)i.Current.Evaluate("string(@parentSquare)"));current[3] =
new SqlInt16(isSash);current[4] =
new SqlInt16(isGlazingBead);current[5] =
new SqlDouble((double)i.Current.Evaluate("number(@width)"));current
=
new SqlDouble((double)i.Current.Evaluate("number(@height)"));current[7] =
new SqlInt16(glassNumber);current
=
new SqlDouble(glassWidth);current[9] =
new SqlDouble(glassHeight); return true;}
else return false;}
public Object Current{
get{
return current;}
}
private short IsSash(string squareId){
string xPath = "descendant::dsc:Square[@id=\"" + squareId + "\"]/parent::dsc:Rod/parent::dsc:Hole[child::dsc:Opening]"; return (short)reader.nav.Select(GetXPathExpression(xPath, reader.nav.NameTable)).Count;}
private short IsGlazingBead(string squareId){
string xPath = "descendant::dsc:Square[@id=\"" + squareId + "\" and ancestor::dsc:Glass]"; return (short)reader.nav.Select(GetXPathExpression(xPath, reader.nav.NameTable)).Count;}
private void GetGlassInfo(string squareId, ref short glassNumber, ref double glassWidth, ref double glassHeight){
string xPath = "descendant::dsc:Square[@id=\"" + squareId + "\"]/ancestor::dsc:Glass"; XPathNavigator node = reader.nav.SelectSingleNode(GetXPathExpression(xPath, reader.nav.NameTable));glassNumber =
short.Parse(node.GetAttribute("number", ""));glassWidth =
double.Parse(node.GetAttribute("width", ""));glassHeight =
double.Parse(node.GetAttribute("height", ""));}
private static XPathExpression GetXPathExpression(string xPath, XmlNameTable nTab){
XPathExpression xPathExpr = XPathExpression.Compile(xPath); XmlNamespaceManager manager = new XmlNamespaceManager(nTab);manager.AddNamespace(
"dsc", "ModelDescriptive");manager.AddNamespace(
"draw", "ModelDraw");manager.AddNamespace(
"display", "Display");manager.AddNamespace(
"thd", "Model3D");xPathExpr.SetContext(manager);
return xPathExpr;}
}
}
public class SLotSquareListReader : IEnumerable{
DataSet data; public SLotSquareListReader(DataSet ds){
data = ds;
}
public IEnumerator GetEnumerator(){
return new SLotSquareEnumerator(this);}
private class SLotSquareEnumerator : IEnumerator{
Object[] current; SLotSquareListReader reader; DataTableReader data; public SLotSquareEnumerator(SLotSquareListReader reader){
this.reader = reader;Reset();
}
public void Reset(){
data =
new DataTableReader(reader.data.Tables[0]);}
public bool MoveNext(){
if (data.Read()){
current =
new Object[14];current[0] =
new SqlInt32(int.Parse(data["Number"].ToString()));current[1] =
new SqlInt32(int.Parse(data["Version"].ToString()));current[2] =
new SqlInt16(short.Parse(data["Position"].ToString()));current[3] =
new SqlInt16(short.Parse(data["SubModel"].ToString()));current[4] =
new SqlInt16(short.Parse(data["Quantity"].ToString()));current[5] =
new SqlString(data["SquareId"].ToString());current
=
new SqlString(data["ParentSquareId"].ToString());current[7] =
new SqlInt16(short.Parse(data["IsSash"].ToString()));current
=
new SqlInt16(short.Parse(data["IsGlazingBead"].ToString()));current[9] =
new SqlDouble(double.Parse(data["Width"].ToString()));current[10] =
new SqlDouble(double.Parse(data["Height"].ToString()));current[11] =
new SqlInt16(short.Parse(data["GlassNumber"].ToString()));current[12] =
new SqlDouble(double.Parse(data["GlassWidth"].ToString()));current[13] =
new SqlDouble(double.Parse(data["GlassHeight"].ToString())); return true;}
else return false;}
public Object Current{
get{
return current;}
}
}
}
}
hwendel
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
Evan Haklar
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
jonathan03
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
Batistuta
This is the regular data. The XML is at the bottom and has to be added manually (either in Management Studio or using an application). The XML should be added in the corresponding field in the ContenidoPAFBlob database.
By the way, I tried using nodes() in a T-SQL TVF and worked, but the performance is not as good as using a CLR TVF. I think using nodes(), the performance is sacrificed quite a bit. I read an alternative would be using XML indexes, but since I'm dealing with big documents, I wonder how feasible is this in terms of storage.
insert
into paf (Numero,Version
,RowId
,Alias
,EntityId
,FechaSolicitud
, Type,NumerationId
,Referencia
,UserCode
,Divisa
,NumeroPedido
,CIP
,FechaConfirmacion
,Confirmado
,ReadyToProdDate
,IsActive
,NombreVersion
,SubOrderNumber
,VersionCopiaDe
,CodigoCliente
,Nombre
,Domicilio
,Domicilio2
,Localidad
,CodigoPostal
,Provincia
,Pais
,Telefono
,NIF
,CustomerRoleId
,NombreTarifa
,NombreTarifaOpcional
,GoalTariff
,ResellerTariff
,ResellerSalesTariff
,Importe
,GoalAmount
,SubTotal
,IVA
,RecargoEquivalencia
,Obra
,DireccionObra
,DireccionObra2
,CodigoPostalObra
,LocalidadObra
,ProvinciaObra
,PaisObra
,TelefonoObra
,ShipToFax
,ShipToContactName
,ShipToComments
,ShipToAreaCode
,SemanaEntrega
,RealDeliveryWeek
,FechaEntrega
,RealDeliveryDate
,Urgente
,Provisional
,NombreFactura
,DireccionFactura
,DireccionFactura2
,CodigoPostalFactura
,LocalidadFactura
,ProvinciaFactura
,PaisFactura
,TelefonoFactura
,BillToComments
,Comentario
,Anotacion
,Sucio
,RecalculateOnLoad
,IncluirEnFacturacion
,Cerrado
,AcceptedDate
,FechaRevision
,FechaDespiece
,FechaEntradaTaller
,FechaSalidaTaller
,FechaMontaje
,FechaFacturado
,FechaPagado
,CommissionPaymentDate
,FechaCancelado
,CancellationReasonId
,FechaContabilizado
,FechaCompras
,StockUpdateDate
,ShippingDate
,SalesmanCode
,Salesman2Code
,RecargoComercial
,Certificacion
,Retencion
,RetentionType
,TotalCertificacionAnterior
,FormaDePago
,CantidadACuenta
,InvoicingType
,IdOpciones
,ShopNumber
,ShopVersion
,LanguageId
,Severity
,CommissionPercentage
,User1
,User2
,XMLParam
,ProductionPlantCode
,CommissionXML
,CommissionBlocked
,DrawingVisualPropertiesXML
)values
(2004694,1
, newid(), '0002004694200700000000013AE43A22-55C2-4DBF-A7B8-B3C055256E8B0000000000000000000000000000000000000000', newid(), getdate(),2
,1
, NULL,561
, 'American',1
, NULL, getdate(),1
, getdate(),0
, NULL,0
,0
,800091
, 'A & M Construction, Inc.', 'P.O. Box 903', NULL, 'Inola', '74036', 'OK', NULL, '918-543-2089', '0',3
, 'Net', NULL, 'Net', NULL, NULL,1625.09
,0
,1625.09
,0
,0
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'LOCAL',0
,0
, NULL, NULL,1
,0
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,0
,0
,0
,0
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,89
, NULL,0
,0
, NULL,1
,0
, '1',0
,0
,0
,0
,0
,0
,4
,0
, NULL, 'OKC', '<Info><TaxSched>NONTAX </TaxSched></Info>',0
, '<Commission SalesDocNumber="2004694" SalesDocVersion="1" CreationDate="2007/02/21" SubTotal="1625.09" TotalCommission="0" CommissionPercentage="0"><Items><Item Position="0" GoalUnitPrice="0" UnitPrice="162.9" ItemRebate="0" Quantity="1" ProductType="-1" Code="180 4070" Nomenclature="" FinalUnitPrice="162.9" Rebate="0"><PriceGroups><PriceGroup Number="1" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="2" Percentage="100" Amount="162.9" GroupGoalPrice="0" GroupSubTotal="162.9" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="3" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="4" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="5" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="6" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/></PriceGroups></Item><Item Position="1" GoalUnitPrice="0" UnitPrice="442.93" ItemRebate="0" Quantity="2" ProductType="-1" Code="180 TRIPLE 9070" Nomenclature="" FinalUnitPrice="442.93" Rebate="0"><PriceGroups><PriceGroup Number="1" Percentage="6.09577134084393" Amount="27" GroupGoalPrice="0" GroupSubTotal="27" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="2" Percentage="93.9042286591561" Amount="415.93" GroupGoalPrice="0" GroupSubTotal="415.93" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="3" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="4" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="5" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="6" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/></PriceGroups></Item><Item Position="2" GoalUnitPrice="0" UnitPrice="192.11" ItemRebate="0" Quantity="3" ProductType="-1" Code="180 TWIN 4044" Nomenclature="" FinalUnitPrice="192.11" Rebate="0"><PriceGroups><PriceGroup Number="1" Percentage="7.02722398625787" Amount="13.5" GroupGoalPrice="0" GroupSubTotal="13.5" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="2" Percentage="92.9727760137421" Amount="178.61" GroupGoalPrice="0" GroupSubTotal="178.61" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="3" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="4" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="5" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/><PriceGroup Number="6" Percentage="0" Amount="0" GroupGoalPrice="0" GroupSubTotal="0" TotalCommission="0" CommissionPercentage="0"/></PriceGroups></Item></Items><SubTotals><SubTotal Number="1" Kind="2" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="2" Kind="2" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="3" Kind="2" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="4" Kind="2" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="5" Kind="4" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="6" Kind="2" Amount="0" Percentage="0"><Items><Item Position="0" PreviousPrice="162.9" NewPrice="162.9"/><Item Position="1" PreviousPrice="442.93" NewPrice="442.93"/><Item Position="2" PreviousPrice="192.11" NewPrice="192.11"/></Items></SubTotal><SubTotal Number="7" Kind="4" Amoun