-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathxml.sql
68 lines (67 loc) · 2.19 KB
/
xml.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
select Tag,
Parent,
[Export!1!],
[Order!2!],
[Order!2!OrderID!ELEMENT],
[Order!2!OrderDate!ELEMENT],
[Order!2!ShipAddress1!ELEMENT],
[Order!2!ShipAddress2!ELEMENT],
[Order!2!ShipCity!ELEMENT],
[Order!2!ShipCompanyName!ELEMENT],
[Order!2!ShipCountry!ELEMENT],
[Order!2!ShipFirstName!ELEMENT],
[Order!2!ShipLastName!ELEMENT],
[Order!2!ShipResidential!ELEMENT],
[Order!2!ShipPostalCode!ELEMENT],
[Order!2!ShipState!ELEMENT],
[OrderDetails!3!],
[OrderDetails!3!OrderDetailID!ELEMENT],
[OrderDetails!3!OrderID!ELEMENT],
[OrderDetails!3!ProductCode!ELEMENT],
[OrderDetails!3!ProductID!ELEMENT],
[OrderDetails!3!ProductName!ELEMENT],
[OrderDetails!3!Quantity!ELEMENT]
from
(
Select 1 as Tag,
Null as Parent,
0 as sort,
Null as 'Export!1!',
Null as 'Order!2!',
Null as 'Order!2!OrderID!ELEMENT',
Null as 'Order!2!OrderDate!ELEMENT',
Null as 'Order!2!ShipAddress1!ELEMENT',
Null as 'Order!2!ShipAddress2!ELEMENT',
Null as 'Order!2!ShipCity!ELEMENT',
Null as 'Order!2!ShipCompanyName!ELEMENT',
Null as 'Order!2!ShipCountry!ELEMENT',
Null as 'Order!2!ShipFirstName!ELEMENT',
Null as 'Order!2!ShipLastName!ELEMENT',
Null as 'Order!2!ShipResidential!ELEMENT',
Null as 'Order!2!ShipPostalCode!ELEMENT',
Null as 'Order!2!ShipState!ELEMENT',
Null as 'OrderDetails!3!',
Null as 'OrderDetails!3!OrderDetailID!ELEMENT',
Null as 'OrderDetails!3!OrderID!ELEMENT',
Null as 'OrderDetails!3!ProductCode!ELEMENT',
Null as 'OrderDetails!3!ProductID!ELEMENT',
Null as 'OrderDetails!3!ProductName!ELEMENT',
Null as 'OrderDetails!3!Quantity!ELEMENT'
union all
select
2 as Tag,
1 as Parent,orderNumber as sort,Null,
Null,
orderNumber,OrderDate,rtrim(ShipAddress1),rtrim(ShipAddress2),rtrim(ShipCity),rtrim(ShipCompanyName),rtrim(ShipCountry),
rtrim(ShipFirstName),rtrim(ShipLastName),ShipResidential,rtrim(ShipPostalCode),ShipState,Null,Null,
null,null,null,null,null
from Orders
union all
select
3 as Tag,
2 as Parent,OrderID as sort,null,null,null,null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,
OrderDetailID,OrderID,RTRIM(ProductCode),ProductID,RTRIM(ProductName),Quantity
from Order_Details inner join GIFT on GIFT.SKU = Order_Details.ProductCode
) A
order by sort
FOR XML EXPLICIT