question

Hi All, I have written below qry select (SELECT value FROM TmsConfiguration where keyname='ShipmentCreate_ContainerTypeCode' )AS ContainerTypeCode , Quantity , Length , Width , Height , ScaledWeight as [ContainerShippingInformation/ScaledWeight], Volume as [ContainerShippingInformation/Volume] , NominalWeight as [ContainerShippingInformation/NominalWeight] , Pieces as [ContainerShippingInformation/Pieces], ContainerDescription , (select value from TmsConfiguration where keyname='ShipmentCreate_IgnoreContainerOrientationsFlag') as [IgnoreContainerOrientationsFlag], (SELECT VALUE FROM TmsConfiguration where keyname='ShipmentCreate_IgnoreWeightByFreightClassFlag') as [IgnoreWeightByFreightClassFlag], --FreightClassNominalWeight as [WeightByFreightClass/FreightClassNominalWeight], FreightClassCode as [WeightByFreightClass/FreightClassCode], (SELECT VALUE FROM TmsConfiguration where keyname='ShipmentCreate_IgnoreShipmentItemsFlag') as [IgnoreShipmentItemsFlag], (SELECT VALUE FROM TmsConfiguration where keyname='ShipmentCreate_IgnoreReferenceNumbersFlag') as [IgnoreReferenceNumbersFlag], (SELECT value FROM TmsConfiguration where keyname='ShipmentCreate_ReferenceNumberTypeCode_7') as [ReferenceNumberStructure/ReferenceNumberTYPECode], ReferenceNumber7 as [ReferenceNumberStructure/ReferenceNumber] , (SELECT value FROM TmsConfiguration where keyname='ShipmentCreate_ReferenceNumberTypeCode_8') as [ReferenceNumberStructure/ReferenceNumberTYPECode], ReferenceNumber8 as [ReferenceNumberStructure/ReferenceNumber] from #ShipmentCreate for xml path('Container') ------------ Out put of above is Pieces 2 0 0 0 0 0 0 2 Classic Matt Twin True False True False T_IID 1605 T_COM 440 Pieces 3 0 0 0 0 0 0 3 Classic Matt Twin True False True False T_IID 1601 T_COM 440 but i want as Pieces 2 0 0 0 0 0 0 2 Classic Matt Twin True False True False T_IID 1605 T_COM 440 Pieces 3 0 0 0 0 0 0 3 Classic Matt Twin True False True False T_IID 1601 T_COM 440 In Short my qry shows output as T_IID 1605 T_COM 440 I am generating tag for item detail kind of information.I want seprate node for ReferenceNumberStructure but since this name is same for below node as well hence sql treating as single node only. I need output as below T_IID 1601 T_COM 440