II. Case I
(a) XML format which has sale voucher header and detail information
<Sale>
<Head>
<VoucherNo>SG0234234</VoucherNo>
<SaleDate>2014-03-28T00:00:00</SaleDate>
<CounterNo>D001</CounterNo>
<CashierNo>AFRIA</CashierNo>
<CustomerNo>SAFRA</CustomerNo>
<Remarks>SAFRA MEMBER DISCOUNT</Remarks>
</Head>
<Body>
<Item>
<ItemNo>PTISSUE</ItemNo>
<ItemName>PENTA TISSUE BOX</ItemName>
<ItemPrice>3.96</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
<Item>
<ItemNo>UMB</ItemNo>
<ItemName>UMBERELLA</ItemName>
<ItemPrice>10.99</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
<Item>
<ItemNo>COLMW</ItemNo>
<ItemName>COLGATE MOUTH WASH</ItemName>
<ItemPrice>17.45</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
</Body>
</Sale>
(b) SQL query to generate above xml format
declare @voucherno as varchar(20)
set @voucherno = 'SG0234234'
SELECT V.VOUCHER_NO as [Head/VoucherNo]
,V.SALE_DATE as [Head/SaleDate]
,V.COUNTER_NO as [Head/CounterNo]
,V.CASHIER_NO as [Head/CashierNo]
,V.CUSTOMER_NO as [Head/CustomerNo]
,V.REMARKS as [Head/Remarks]
,(
SELECT I.ITEM_NO as [Item/ItemNo]
,I.ITEM_NAME as [Item/ItemName]
,cast(I.ITEM_PRICE as decimal(10,2)) as [Item/ItemPrice]
,I.ITEM_QTY as [Item/ItemQty]
,I.ITEM_REMARK as [Item/Remark]
FROM SALE_VOUCHER_ITEM I
WHERE I.VOUCHER_NO = @voucherno
FOR XML PATH(''),Root('Body'), TYPE
)
FROM SALE_VOUCHER V
WHERE V.VOUCHER_NO = @voucherno
FOR XML PATH(''), ROOT('Sale')
II. Case II
(a) XML format which displays sale voucher header and detail information, some header info is displayed at detail level
II. Case II
(a) XML format which displays sale voucher header and detail information, some header info is displayed at detail level
<Sale>
<Head>
<VoucherNo>SG0234234</VoucherNo>
<SaleDate>2014-03-28T00:00:00</SaleDate>
<CounterNo>D001</CounterNo>
<CashierNo>AFRIA</CashierNo>
</Head>
<Body>
<CustomerNo>SAFRA</CustomerNo>
<Remarks>SAFRA MEMBER DISCOUNT</Remarks>
<Item>
<ItemNo>PTISSUE</ItemNo>
<ItemName>PENTA TISSUE BOX</ItemName>
<ItemPrice>3.96</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
<Item>
<ItemNo>UMB</ItemNo>
<ItemName>UMBERELLA</ItemName>
<ItemPrice>10.99</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
<Item>
<ItemNo>COLMW</ItemNo>
<ItemName>COLGATE MOUTH WASH</ItemName>
<ItemPrice>17.45</ItemPrice>
<ItemQty>1</ItemQty>
</Item>
</Body>
</Sale>(b) SQL query to generate above xml format
declare @voucherno as varchar(20)
set @voucherno = 'SG0234234'
SELECT V.VOUCHER_NO as [Head/VoucherNo]
,V.SALE_DATE as [Head/SaleDate]
,V.COUNTER_NO as [Head/CounterNo]
,V.CASHIER_NO as [Head/CashierNo]
,( SELECT V.CUSTOMER_NO as [CustomerNo]
,V.REMARKS as [Remarks]
,(
SELECT I.ITEM_NO as [Item/ItemNo]
,I.ITEM_NAME as [Item/ItemName]
,cast(I.ITEM_PRICE as decimal(10,2))
as [Item/ItemPrice]
,I.ITEM_QTY as [Item/ItemQty]
,I.ITEM_REMARK as [Item/Remark]
FROM SALE_VOUCHER_ITEM I
WHERE I.VOUCHER_NO = @voucherno
FOR XML PATH(''), TYPE
)
FROM SALE_VOUCHER V
WHERE V.VOUCHER_NO = @voucherno
FOR XML PATH('Body'),TYPE
)
FROM SALE_VOUCHER V
WHERE V.VOUCHER_NO = @voucherno
FOR XML PATH(''), ROOT('Sale')
.JPG)
No comments:
Post a Comment