SQL : How to generate XML in MSSQL ( table => xml )

I. Table Design


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


<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')
 

No comments:

Post a Comment

Penang 4D3N Free & Easy - Intro

ပီနန္က်ြန္း၊  မေလးရွားနုိင္ငံ။ ေအာက္တုိဘာ ၆ ရက္မွ ၉ ရက္။ စေန၊ တနဂၤေနြ၊ တနၤလာ၊ အဂၤါ။ ဒီလုိေတြ စီစဥ္ၾကတာ။ ဒီခရီးကေတာ့ သြားခါနီး ၂ ရက္အ...