使用标记将SQL行转换为xml

I have the below query:

我有以下查询：

select 
t.location_id as Station_ID 
 ,' ' as Trans_register 
 ,timp.hose_num as Trans_Pump 
 ,t.invoice as Trans_Receipt 
 ,t.pos_date as Trans_Date_Time 
 ,' ' as Trans_Batch 
 ,' ' as Trans_Batch_Sequence 
 ,CASE 
WHEN im.org_id = 125 and l.chain_id = 10 THEN "EC" 
WHEN im.org_id = 125 and l.chain_id <> 10 THEN "ER" 
WHEN im.org_id = 165 and l.chain_id = 10 and t.stmt_id <> -4 THEN "DC" 
WHEN im.org_id = 165 and l.chain_id = 10 and t.stmt_id = -4 THEN "IC" 
WHEN im.org_id = 165 and l.chain_id <> 10 and t.stmt_id <> -4 THEN "DR" 
WHEN im.org_id = 165 and l.chain_id <> 10 and t.stmt_id = -4 THEN "IR" 
ELSE "EC" END as Trans_Origin 
,t.trans_date as Trans_Process_Date_Time 
,CASE 
 WHEN l.chain_Id <> 10 then t.pref_total 
 ELSE 0 END as Trans_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltg.amount 
 ELSE 0 END as Trans_GST_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltp.amount 
 ELSE 0 END as Trans_PST_Amount 
,' ' as Loyalty_Program 
,' ' as Loyalty_Account_Number 
,' ' as Loyalty_Fee 
,CASE 
 WHEN l.chain_Id <> 10 then t.pref_total 
 ELSE 0 END as Trans_Loyalty_Amount 


,tl.line_id + 1 as Product_Sequence 
,CASE 
 WHEN tl.cat in ("DEF","DEFD") then 4194304 
 --DEV need to add that if it's not DEF, DEFD and fuel type is 0 using the tl.cat but that wasn't working for me...need smarties to do that! 
 ELSE tl.fuel_type end as Product_Code 
,tl.qty as Product_Qty --will always be Litres since Canada 
,CASE 
 WHEN l.chain_id <> 10 then tl.amt else 0 end as Product_Amount 
,' ' as Product_Loyalty_Amount 
,tltf.amount as Product_FET_Amount 
,tltf.tax_rate as Product_FET_Unit_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltg.amount 
 ELSE 0 END as Product_GST_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltg.tax_rate 
 ELSE 0 END as Product_GST_Unit_Amount 
,tltpf.amount as Product_PFT_Amount 
,tltpf.tax_rate as Product_PFT_Unit_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltp.amount 
 ELSE 0 END as Product_PST_Amount 
,CASE 
 WHEN l.chain_id <> 10 THEN tltp.tax_rate 
 ELSE 0 END as Product_PST_Unit_Amount 
,tltv.amount as Product_MFT_Amount 
,tltv.tax_rate as Product_MFT_Unit_Amount 


,tl.line_id + 1 as Tender_Sequence 
,t.Carrier_Id as Tender_Account 
,' ' as Tender_Unit_Card_Number 
,CASE 
 WHEN im.org_id = 165 then "19" ELSE "18" END as Tender_Type 
,CASE 
 WHEN l.chain_Id <> 10 then t.pref_total 
 ELSE 0 END as Tedner_Amount 
,' ' as Tender_Fee_Amt 
,ca.expiredate as Tender_Exp_date 
,t.auth_code as Authorization_Code 
,CASE WHEN t.extra_3 in (3,7) then "N" else "Y" END as Authorization_Method 
,' ' as Fi_Termid 
,' ' as Fi_Sequence 
,tiu.info as Unit_Number 
,tio.info as Odometer_Number 
,tid.info as Driver_Number 
,tip.info as Trip_Number 
,' ' as Reference_Number 
,tit.info as Trailer_Number 
,' ' as Trailer_Hub 
,tihb.info as Tractor_Hub 
,' ' as Check_Digit 
,' ' as FI_Response_Code 
,left(t.card_num,6) as FI_ISO_Code 

from 
transaction as t 

left outer join 
trans_imperial as timp 
on t.trans_id = timp.trans_id 
left outer join location as l 
  on t.location_id = l.location_id 
left outer join contract as c 
  on t.contract_id = c.contract_id 
left outer join issuer_misc as im 
 on c.issuer_id = im.issuer_id 
left outer join trans_line_tax as tltg 
on t.trans_id = tltg.trans_id 
and tltg.tax_cd IN ("GST","HST","FNT") and tltg.gross_net_flag = "N" and tltg.exempt_flag = "N" 
left outer join trans_line_tax as tltp 
on t.trans_id = tltp.trans_id 
and tltp.tax_cd IN ("PST","QST") and tltp.gross_net_flag = "N" and tltp.exempt_flag = "N" 
left outer join trans_line as tl 
on t.trans_id = tl.trans_id 

left outer join trans_line_tax as tltf 
on t.trans_id = tltf.trans_id 
and tltf.tax_cd = "FET" and tltf.gross_net_flag = "N" and tltf.exempt_flag = "N" 

left outer join trans_line_tax as tltpf 
on t.trans_id = tltpf.trans_id 
and tltpf.tax_cd = "PFT" and tltpf.gross_net_flag = "N" and tltpf.exempt_flag = "N" 

left outer join trans_line_tax as tltv 
on t.trans_id = tltv.trans_id 
and tltv.tax_cd = "VTT" and tltv.gross_net_flag = "N" and tltv.exempt_flag = "N" 

left outer join cards as ca 
on t.card_num = ca.card_num 

left outer join trans_info as tiu 
on t.trans_id = tiu.trans_id 
and tiu.type = "UNIT" 

left outer join trans_info as tio 
on t.trans_id = tio.trans_id 
and tio.type = "ODRD" 

left outer join trans_info as tid 
on t.trans_id = tid.trans_id 
and tid.type = "DRID" 

left outer join trans_info as tip 
on t.trans_id = tip.trans_id 
and tip.type = "TRIP" 

left outer join trans_info as tit 
on t.trans_id = tit.trans_id 
and tit.type = "TRLR" 

left outer join trans_info as tihb 
on t.trans_id = tihb.trans_id 
and tihb.type = "HBRD" 

where 
 t.extra_5 = 20161204 
 and l.src_country = "CAN"

The above query when executed gives result as below:

执行时的上述查询给出如下结果：

Where as I want the result in xml format as below:

我想要的结果是xml格式，如下所示：

<?xml version=”1.0” encoding=”UTF-16” standalone=”yes”?>
<EFS_92_Completion_XML_FILE>
<File_Creation_Date>201605271023</File_Creation_Date>
<Record_Count>1</Record_Count>                      
<Trans>                                             
<Station_ID>9876</Station_ID>                               
<Trans_Register>99</Trans_Register>                             
<Trans_Pump>01</Trans_Pump>                                 
<Trans_Receipt>1234567</Trans_Receipt>                          
<Trans_Date_Time>201605271023</Trans_Date_Time>                     
<Trans_Batch>1234</Trans_Batch>                             
<Trans_Batch_Sequence>101</Trans_Batch_Sequence>                    
<Trans_Origin>RI</Trans_Origin>                             
<Trans_Process_Date_Time>201605281822</Trans_Process_Date_Time>         
<Trans_Amount>10.68</Trans_Amount>                          
<Trans_GST_Amount>0.51</Trans_GST_Amount>                       
<Trans_PST_Amount></Trans_PST_Amount>                       
<Loyalty_Program>HLR</Loyalty_Program>
<Loyalty_Account_Number>7076101117512061 </Loyalty_Account_Number>
<Loyalty_Fee></Loyalty_Fee>
<Trans_Loyalty_Amount>10.68</Trans_Loyalty_Amount>
<Product>
<Product_Sequence>1</Product_Sequence>
<Product_Code>0131</Product_Code>
<Trailer_Number></Trailer_Number>
<Trailer_Hub></Trailer_Hub>
<Tractor_Hub></Tractor_Hub>
<Check_Digit></Check_Digit>
<FI_Response_Code></FI_Response_Code >
<FI_ISO_Code></FI_ISO_Code >
</Tender>
</Trans>
</EFS_92_Completion_XML_FILE>

How can I convert the SQL rows into the xml format file with tags?. I have tried adding the FOR XMLclause but I haven't been successful.

如何将SQL行转换为带标签的xml格式文件？我试过添加FOR XMLclause但是我还没有成功。

1 个解决方案

#1

Have a look into the FOR XML construct again. There are lots of really good examples here on SO. You are on the right track with your last comment, FOR XML PATH will work for this if you place it after your WHERE clause.

再次查看FOR XML构造。 SO上有很多非常好的例子。如果将它放在WHERE子句之后，FOR XML PATH将适用于此。

SELECT Field1, Field2, Field3 
FROM Table1 
WHERE Field1 = SomeValue 
FOR XML PATH('ROW'), ROOT('XML')

This has some fantastic examples also...

这也有一些很棒的例子......

https://msdn.microsoft.com/en-us/library/bb510462.aspx

#1