FOR XML child nodes depend on column value
-
I have to do a lot of work on an SQL database that has some issues carried forward from an old legacy database, it still uses line typing within the same table.
I am trying to extract data as XML from these tables.
Briefly, the OrderDetails table can contain either stock lines or comment lines.
The desired result is:
<SalesOrders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Orders>
<OrderHeader>
<XOrder>WKMH10000 </XOrder>
<CustomerPoNumber>1234</CustomerPoNumber>
<Customer>BOL1000</Customer>
<OrderDate>2011-10-10</OrderDate>
<RequestedShipDate>2011-10-15</RequestedShipDate>
</OrderHeader>
<OrderDetails>
<StockLine>
<CustomerPoLine>1</CustomerPoLine>
<LineActionType>A</LineActionType>
<StockCode>WS8x30</StockCode>
<OrderQty>1200.000</OrderQty>
<Price>5.23000</Price>
</StockLine>
<CommentLine>
<CustomerPoLine>2</CustomerPoLine>
<LineActionType>A</LineActionType>
<Comment>This is a comment line</Comment>
<AttachedLineNumber>1</AttachedLineNumber>
<CommentType>A</CommentType>
</CommentLine>
<StockLine>
<CustomerPoLine>3</CustomerPoLine>
<LineActionType>A</LineActionType>
<StockCode>WS8x40</StockCode>
<OrderQty>800.000</OrderQty>
<Price>5.50000</Price>
</StockLine>
</OrderDetails>
</Orders>
</SalesOrders>
I get this without the CustomerOrderLine2 (The comment line) by using this:
select XOrder as [OrderHeader/XOrder],[CustomerPoNumber] as [OrderHeader/CustomerPoNumber],
Customer as [OrderHeader/Customer],
CONVERT(VARCHAR(10),OrderDate,120) as [OrderHeader/OrderDate],
CONVERT(VARCHAR(10),RequestedShipDate,120) as [OrderHeader/RequestedShipDate],
(Select
(select CustomerPoLine as [StockLine/CustomerPoLine],
'A' as [StockLine/LineActionType],
StockCode as [StockLine/StockCode],
OrderQty as [StockLine/OrderQty],
Price as [StockLine/Price]
from OrderDetails
where OrderDetails.XOrder = OrderHeaders.XOrder
AND LineType = 1
for xml path(''),type)
) as OrderDetails
from OrderHeaders
for xml path('Orders'),elements XSINIL,
root('SalesOrders')
If I vary it slightly I get the CommentLine. Like this:
select XOrder as [OrderHeader/XOrder],[CustomerPoNumber] as [OrderHeader/CustomerPoNumber],
Customer as [OrderHeader/Customer],
CONVERT(VARCHAR(10),OrderDate,120) as [OrderHeader/OrderDate],
CONVERT(VARCHAR(10),RequestedShipDate,120) as [OrderHeader/RequestedShipDate],
(Select
(select CustomerPoLine as [CommentLine/CustomerPoLine],
'A' as [CommentLine/LineActionType],
Comment as [CommentLine/Comment],
AttachedLineNumber as [CommentLine/AttachedLineNumber],
CommentType as [CommentLine/CommentType]
from OrderDetails
where OrderDetails.XOrder = OrderHeaders.XOrder
AND LineType = 6
for xml path(''),type)
) as OrderDetails
from OrderHeaders
for xml path('Orders'),elements XSINIL,
root('SalesOrders')
I have tried various ways of using CASE or sub-queries but with no success.
Does anybody out there have a solution for me?
Thanks, Ken
-
Can you post some sample data for the source tables?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks for your reply. Yes I can post some data. What format would you like it in?
The extract that I posted as the desired result are in fact a full data set in XML format.
-
Use this
http://www.sqlservercentral.com/articles/Best+Practices/61537
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Hope this is in order.
--Create 2 tables OrderHeaders and OrderDetails
/****** Object: Table [dbo].[OrderHeaders] Script Date: 11/24/2011 14:23:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderHeaders](
[OrderID] [int] IDENTITY(10000,1) NOT NULL,
[XOrder] AS (([Source]+[Salesperson])+CONVERT([char](6),[OrderID],0)),
[Source] [char](1) COLLATE Latin1_General_BIN NOT NULL,
[Salesperson] [char](3) COLLATE Latin1_General_BIN NOT NULL,
[CustomerPoNumber] [varchar](30) COLLATE Latin1_General_BIN NOT NULL,
[Customer] [char](7) COLLATE Latin1_General_BIN NOT NULL,
[OrderDate] [datetime] NOT NULL,
[RequestedShipDate] [datetime] NOT NULL,
[CustomerName] [varchar](30) COLLATE Latin1_General_BIN NULL,
[MultiShipCode] [char](5) COLLATE Latin1_General_BIN NULL,
[ShipAddress1] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ShipAddress2] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ShipAddress3] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ShipAddress4] [varchar](40) COLLATE Latin1_General_BIN NULL,
[ShipAddress5] [varchar](40) COLLATE Latin1_General_BIN NULL,
CONSTRAINT [PK_OrderHeaders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[OrderDetails] Script Date: 11/24/2011 14:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails](
[XOrder] [char](10) COLLATE Latin1_General_BIN NOT NULL,
[LineType] [varchar](15) COLLATE Latin1_General_BIN NOT NULL,
[CustomerPoLine] [int] NOT NULL,
[StockCode] [varchar](30) COLLATE Latin1_General_BIN NOT NULL,
[OrderQty] [decimal](10, 3) NULL,
[Price] [decimal](15, 5) NULL,
[OrderUom] [char](3) COLLATE Latin1_General_BIN NULL,
[PriceUom] [char](3) COLLATE Latin1_General_BIN NULL,
[PriceCode] [char](1) COLLATE Latin1_General_BIN NULL,
[Warehouse] [char](2) COLLATE Latin1_General_BIN NULL,
[Comment] [nchar](45) COLLATE Latin1_General_BIN NULL,
[AttachedLineNumber] [int] NULL,
[CommentType] [char](1) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--Add data to OrderHeaders
SET IDENTITY_INSERT OrderHeaders ON
INSERT INTO OrderHeaders (XOrder,CustomerPoNumber,Customer,OrderDate,RequestedShipDate)
SELECT 'WKMH10000 ','1234','BOL1000','Oct 10 2011 12:00AM','Oct 15 2011 12:00AM' UNION ALL
SELECT 'WKMH10001 ','1234','BOL1000','Oct 10 2011 12:00AM','Oct 15 2011 12:00AM'
SET IDENTITY_INSERT OrderHeaders OFF
--Add data to OrderDetails
SET IDENTITY_INSERT OrderDetails ON
INSERT INTO OrderDetails(XOrder,LineType,CustomerPoLine,StockCode,OrderQty,Price,OrderUom,PriceUom,PriceCode,Warehouse,Comment,AtachedLineNumber,CommentType)
SELECT 'WKMH10000','1','1','WS8x30','1200.000','5.23000','BOX','BOX','A','WT','NULL','NULL','NULL' UNION ALL
SELECT 'WKMH10000','6','2','Com','NULL','NULL','NULL','NULL','NULL','NULL','This is a comment line','1','A, UNION ALL
SELECT 'WKMH10000','1','3','WS8x40','800.000','5.50000','BOX','BOX','A','WT','NULL','NULL','NULL' UNION ALL
SELECT 'WKMH10000','1','4','WS6x25','400.000','4.30000','BOX','BOX','A','WT','NULL','NULL','NULL','UNION ALL
SELECT 'WKMH10001','1','1','PN10x100','15000.000','0.11000','EA','EA','B','ZJ','NULL','NULL','NULL'
SET IDENTITY_INSERT OrderDetails OFF
-
Is this what you're after?
select XOrder as [OrderHeader/XOrder],[CustomerPoNumber] as [OrderHeader/CustomerPoNumber],
Customer as [OrderHeader/Customer],
CONVERT(VARCHAR(10),OrderDate,120) as [OrderHeader/OrderDate],
CONVERT(VARCHAR(10),RequestedShipDate,120) as [OrderHeader/RequestedShipDate],
(Select
(select case when LineType = 1 then CustomerPoLine end as [StockLine/CustomerPoLine],
case when LineType = 1 then 'A' end as [StockLine/LineActionType],
case when LineType = 1 then StockCode end as [StockLine/StockCode],
case when LineType = 1 then OrderQty end as [StockLine/OrderQty],
case when LineType = 1 then Price end as [StockLine/Price],
case when LineType = 6 then CustomerPoLine end as [CommentLine/CustomerPoLine],
case when LineType = 6 then 'A' end as [CommentLine/LineActionType],
case when LineType = 6 then Comment end as [CommentLine/Comment],
case when LineType = 6 then AttachedLineNumber end as [CommentLine/AttachedLineNumber],
case when LineType = 6 then CommentType end as [CommentLine/CommentType]
from OrderDetails
where OrderDetails.XOrder = OrderHeaders.XOrder
AND LineType in (1,6)
order by CustomerPoLine
for xml path(''),type)
) as OrderDetails
from OrderHeaders
for xml path('Orders'),elements XSINIL,
root('SalesOrders')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Thanks very much Mark.
I got close but had the case END in the wrong place
CASE LineType WHEN 1 THEN (StockCode as [StockLine/StockCode])
ELSE (Comment as [CommentLine/Comment]) END,
Your's works perfectly!:-):-)
-
ken-1133369 (11/24/2011)
Thanks very much Mark.I got close but had the case END in the wrong place
CASE LineType WHEN 1 THEN (StockCode as [StockLine/StockCode])
ELSE (Comment as [CommentLine/Comment]) END,
Your's works perfectly!:-):-)
Thanks for the feedback
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply