嵌套sql查询的XML

配置SQL的XML

001 <?xml version="1.0" encoding="utf-8" ?> 002 <template name="booking" type="export" desc="" filename="321651[MMdd][XXX]"> 003 004 <record no="00" desc="Head record" commandtype="Text" sqltext=" select Top 1 IDENTITY_CODE,IDENTITY_NAME,(select RTRIM(LTRIM(Carrier)) AS Carrier from carrierbookingInfo where Booking_No=@Booking_No) Carrier from TBL_SYS_IDENTITY where IDENTITY_CODE=@LoginId and DELETE_FLAG = 0 "> 005 <params> 006 <param name="@LoginId" datatype="string" size="36" type="input"></param> 007 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 008 </params> 009 <fields> 010 <field desc="Record ID" datatype="int" size="2" type="M">00</field> 011 <field desc="Message Type" datatype="string" size="6" type="M">IFTMBF</field> 012 <field desc="file desc" datatype="string" size="35" type="C">BOOKING</field> 013 <field desc="file function" datatype="string" size="3" type="M">NEW</field> 014 <field desc="sender code" datatype="string" size="13" type="M">BST</field> 015 <field fieldname="Carrier" desc="receiver code" datatype="string" size="13" type="M"></field> 016 <field desc="file create time" datatype="datetime" size="17" type="M" format="yyyyMMddHHmmssfff">[sysdate]</field> 017 <field fieldname="IDENTITY_CODE" desc="creator" datatype="string" size="10" type="C"></field> 018 <field fieldname="IDENTITY_NAME" desc="name of careator" datatype="string" size="35" type="C"></field> 019 </fields> 020 </record> 021 022 <record no="02" desc="Booking Information" commandtype="Text" 023 sqltext="select @CarrierSeqNo Booking_No, 024 (select replace(Shipping_NO,'-','') from ShippingOrder where Shipping_NO=dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)) Number_No, 025 (select Carrier_SO from ShippingOrder where Shipping_NO=dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)) Carrier_SO, 026 (select Carrier_SO from ShippingOrder where Shipping_NO=dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)) Master_Carrier_SO, 027 (select S_FLD_1 from scode where scd_type=0 and Scd_Id = Service) AS Service_Type, 028 (select S_FLD_1 from scode where scd_type=1 and Scd_Id = Shipment_Type) AS Shipment_Type, 029 RTRIM(LTRIM(Carrier)) AS Carrier_Id, 030 RTRIM(LTRIM(Carrier)) AS Carrier, 031 (select MLine_ContractCode from dbo.CustomerInfo where Cust_Type=2 and Cust_Code=Consignee) Contratual_Party_Code, 032 (select Mlog_ContractName from CustomerInfo where Cust_Type = 2 and Cust_Code=Consignee) Contratual_Party, 033 Contract_No, 034 (select S_FLD_1 from scode where scd_type=23 and Scd_Id = Freight_Terms) Freight_Terms,Freight_Terms_At 035 from carrierbookingInfo where Booking_No=@Booking_No"> 036 <params> 037 <param name="@CarrierSeqNo" datatype="string" size="10" type="input"></param> 038 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 039 </params> 040 <fields> 041 <field desc="Record ID" datatype="int" size="2" type="M">02</field> 042 <field fieldname="Booking_No" desc="Booking NO" datatype="string" size="32" type="M" ></field> 043 <field fieldname="Number_No" desc="SUB BOOKING NO" datatype="string" size="32" type="M" ></field> 044 <field fieldname="Carrier_SO" desc="B/L NO" datatype="string" size="20" type="C" ></field> 045 <field fieldname="Master_Carrier_SO" desc="Master B/L NO" datatype="string" size="20" type="C" ></field> 046 <field fieldname="Service_Type" desc="Delivery term" datatype="string" size="9" type="M" ></field> 047 <field fieldname="Shipment_Type" desc="Shipment Type" datatype="string" size="10" type="M" ></field> 048 <field desc="Booking Party" datatype="string" size="35" type="C" ></field> 049 <field fieldname="Carrier_Id" desc="Issue Party Code" datatype="string" size="13" type="M" ></field> 050 <field fieldname="Carrier" desc="Issue Party" datatype="string" size="35" type="M" ></field> 051 <field fieldname="Contratual_Party_Code" desc="Contratual Party Code" datatype="string" size="35" type="C" ></field> 052 <field fieldname="Contratual_Party" desc="Applicant" datatype="string" size="13" type="C" ></field> 053 <field fieldname="Contract_No" desc="Service Contract No." datatype="string" size="15" type="C" ></field> 054 <field fieldname="Freight_Terms" desc="PREPAID OR COLLECT" datatype="string" size="1" type="C" ></field> 055 <field fieldname="Freight_Terms_At" desc="PAYABLE AT (E)" datatype="string" size="5" type="C" ></field> 056 </fields> 057 </record> 058 059 <record no="04" desc="CONTACT PERSON" commandtype="Text" sqltext=" select Top 1 (select MLOG_Entity from carrierbookingInfo where Booking_No=@Booking_No) Mlog_Entity, 060 (select S_FLD_2 from scode A,carrierbookingInfo B where scd_type=3 and B.MLOG_Entity= A.scd_Id and B.Booking_No=@Booking_No) Mlog_Entity_CODE , 061 IDENTITY_CODE,IDENTITY_NAME,EMAIL1,REV1,DESCRIPTION from TBL_SYS_IDENTITY where IDENTITY_CODE=@LoginId and DELETE_FLAG = 0"> 062 <params> 063 <param name="@LoginId" datatype="string" size="10" type="input"></param> 064 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 065 </params> 066 <fields> 067 <field desc="Record ID" datatype="int" size="2" type="M">04</field> 068 <field fieldname="IDENTITY_CODE" desc="CONTACT PERSON ID" datatype="string" size="10" type="C"></field> 069 <field fieldname="IDENTITY_NAME" desc="CONTACT PERSON NAME" datatype="string" size="35" type="M"></field> 070 <field fieldname="EMAIL1" desc="EMAIL/BA" datatype="string" size="35" type="C"></field> 071 <field fieldname="REV1" desc="TEL" datatype="string" size="35" type="C"></field> 072 <field fieldname="Mlog_Entity" desc="MLOG Entity" datatype="string" size="35" type="M"></field> 073 <field fieldname="Mlog_Entity_CODE" desc="MLOG Entity SCV CODE" datatype="string" size="35" type="M"></field> 074 <field fieldname="DESCRIPTION" desc="REMARK" datatype="string" size="35" type="C"></field> 075 </fields> 076 </record> 077 078 <record no="11" desc="VESSEL" commandtype="Text" sqltext="select Feeder_Vessel Vessel,Feeder_Voyage Vyg_No,String trade,RTRIM(LTRIM(Carrier)) AS Carrier_Id,RTRIM(LTRIM(Carrier)) AS Carrier,Feeder_ETD from carrierbookingInfo where Booking_No=@Booking_No"> 079 <params> 080 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 081 </params> 082 <fields> 083 <field desc="Record ID" datatype="int" size="2" type="M">11</field> 084 <field desc="Vessel Code" datatype="string" size="9" type="C"></field> 085 <field fieldname="Vessel" desc="Vessel" datatype="string" size="35" type="M"></field> 086 <field fieldname="Vyg_No" desc="Voyage" datatype="string" size="10" type="M"></field> 087 <field fieldname="Carrier_Id" desc="SHIPPING LINE CODE" datatype="string" size="13" type="C"></field> 088 <field fieldname="Carrier" desc="SHIPPING LINE" datatype="string" size="35" type="C"></field> 089 <field fieldname="Carrier_Id" desc="B/L CARRY CODE" datatype="string" size="13" type="C"></field> 090 <field fieldname="Carrier" desc="B/L CARRY" datatype="string" size="35" type="C"></field> 091 <field fieldname="Feeder_ETD" desc="requested shipment date" datatype="datetime" size="8" type="M" format="yyyyMMdd"></field> 092 <field desc="trade code " datatype="string" size="10" type="C"></field> 093 <field fieldname="trade" desc="trade" datatype="string" size="35" type="C"></field> 094 </fields> 095 </record> 096 097 <record no="12" desc="PORTS" commandtype="Text" 098 sqltext="select (select QUICK_STEP_Code from PortInfo where Port_Code = POR) V_POR, 099 (select Port_Ename from PortInfo where Port_Code = POR) V_POR_Name, 100 (select QUICK_STEP_Code from PortInfo where Port_Code = [Load]) V_Load, 101 (select Port_Ename from PortInfo where Port_Code = [Load]) V_Load_Name, 102 (select QUICK_STEP_Code from PortInfo where Port_Code = Discharge) V_Discharge, 103 (select Port_Ename from PortInfo where Port_Code = Discharge) V_Discharge_Name, 104 (select QUICK_STEP_Code from PortInfo where Port_Code = Transit_Port) V_Transit_Port, 105 (select Port_Ename from PortInfo where Port_Code = Transit_Port) V_Transit_Port_Name, 106 (select QUICK_STEP_Code from PortInfo where Port_Code = POD) V_POD, 107 (select Port_Ename from PortInfo where Port_Code = POD) V_POD_Name 108 from carrierbookingInfo where Booking_No=@Booking_No"> 109 <params> 110 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 111 </params> 112 <fields> 113 <field desc="Record ID" datatype="int" size="2" type="M">12</field> 114 <field fieldname="V_POR" desc="place code of receipt" datatype="string" size="5" type="M"></field> 115 <field fieldname="V_POR_Name" desc="place of receipt" datatype="string" size="35" type="M"></field> 116 <field fieldname="V_Load" desc="LOAD PORT code" datatype="string" size="5" type="C"></field> 117 <field fieldname="V_Load_Name" desc="LOAD PORT" datatype="string" size="35" type="C"></field> 118 <field fieldname="V_Discharge" desc="DISCHARGE PORT code " datatype="string" size="5" type="C"></field> 119 <field fieldname="V_Discharge_Name" desc="DISCHARGE PORT" datatype="string" size="35" type="C"></field> 120 <field fieldname="V_Transit_Port" desc="Transit Port Code" datatype="string" size="5" type="C"></field> 121 <field fieldname="V_Transit_Port_Name" desc="Transit Port Code" datatype="string" size="35" type="C"></field> 122 <field fieldname="V_POD" desc="place of DELIVERY code" datatype="string" size="5" type="M"></field> 123 <field fieldname="V_POD_Name" desc="place of DELIVERY" datatype="string" size="35" type="M"></field> 124 <field desc="final destination code" datatype="string" size="5" type="C"></field> 125 <field desc="final destination" datatype="string" size="35" type="C"></field> 126 </fields> 127 </record> 128 129 <record no="17" desc="REMARKS" commandtype="Text" mc="C" sqltext="select replace(replace(remark,char(10),''),char(13),'') as Remark from carrierbookingInfo where Booking_No=@Booking_No" max="1"> 130 <params> 131 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 132 </params> 133 <fields> 134 <field desc="Record ID" datatype="int" size="2" type="M">17</field> 135 <field fieldname="Remark" desc="REMARKS(1-5)" datatype="string" size="35" type="M" paragraph="5"> </field> 136 </fields> 137 </record> 138 139 <record no="20" desc="SHIPPER" commandtype="Text" sqltext=" select A.Mlog_Entity Shipper,(select B.Scd_EName from scode B where B.scd_type=3 and B.Scd_Id = A.Mlog_Entity) Shipper_Info from carrierbookingInfo A where A.booking_no=@Booking_No " max="1"> 140 <params> 141 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 142 </params> 143 <fields> 144 <field desc="Record ID" datatype="int" size="2" type="M">20</field> 145 <field fieldname="Shipper" desc="SHIPPER CODE" datatype="string" size="13" type="C"></field> 146 <field fieldname="Shipper_Info" desc="SHIPPER(1-5)" datatype="string" size="35" type="M" paragraph="5"></field> 147 </fields> 148 </record> 149 150 <record no="21" desc="CONSIGNEE" commandtype="Text" sqltext="select (select Scv_Code from CustomerInfo where Cust_Type = 2 and Cust_Code=Consignee) Consignee, 151 (select (case when (B.Cust_EName is null) then '' else B.Cust_EName end)+(case when (B.EAddress is null) or (B.EAddress ='') then '' else '/' +B.EAddress end) 152 from CustomerInfo B where B.cust_type=2 and B.cust_code=A.Consignee) Consignee_Info from carrierbookingInfo A where A.booking_no=@Booking_No " max="1"> 153 <params> 154 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 155 </params> 156 <fields> 157 <field desc="Record ID" datatype="int" size="2" type="M">21</field> 158 <field fieldname="Consignee" desc="SHIPPING CODE" datatype="string" size="13" type="C"></field> 159 <field fieldname="Consignee_Info" desc="CONSIGNEE(1-5)" datatype="string" size="35" type="M" paragraph="5"></field> 160 </fields> 161 </record> 162 163 <record no="22" desc="Notify" commandtype="Text" mc="C" sqltext="select case when A.Notify_Party is null or A.Notify_Party='' then A.Consignee else A.Notify_Party end Notify_Party, 164 case when A.Notify_Party is null or A.Notify_Party='' then (case when(select B.Cust_EName+'/' +B.EAddress from CustomerInfo B 165 where B.cust_type=2 and B.cust_code=A.Consignee) is null then A.Consignee else (select B.Cust_EName+'/' +B.EAddress from CustomerInfo B 166 where B.cust_type=2 and B.cust_code=A.Consignee) end) else (case when(select B.Cust_EName+'/' +B.EAddress from CustomerInfo B 167 where B.cust_type=2 and B.cust_code=A.Notify_Party) is null then A.Notify_Party else (select B.Cust_EName+'/' +B.EAddress from CustomerInfo B 168 where B.cust_type=2 and B.cust_code=A.Notify_Party) end) end Notify_Party_Info from carrierbookingInfo A where A.booking_no=@Booking_No" max="1"> 169 <params> 170 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 171 </params> 172 <fields> 173 <field desc="Record ID" datatype="int" size="2" type="M">22</field> 174 <field fieldname="Notify_Party" desc="Notify CODE" datatype="string" size="13" type="C"></field> 175 <field fieldname="Notify_Party_Info" desc="NOTIFY(1-5)" datatype="string" size="35" type="C" paragraph="5"></field> 176 </fields> 177 </record> 178 179 <record no="41" desc="Cargo Of Booking" commandtype="Text" sqltext="select (isnull(Package,0)) Package,dbo.GetPOForEDIBooking_PRE(dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)) PO,(isnull(KGS,0)) KGS,(isnull(CBM,0)) CBM,(isnull(PCS,0)) PCS from carrierbookingInfo where booking_no=@Booking_No" > 180 <params> 181 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 182 </params> 183 <fields> 184 <field desc="Record ID" datatype="int" size="2" type="M">41</field> 185 <field desc="Cargo Sequence No." datatype="int" size="3" type="M">1</field> 186 <field fieldname="PO" desc="Cargo Code" datatype="string" size="20" type="C"></field> 187 <field desc="SKU" datatype="string" size="8" type="C"></field> 188 <field fieldname="Package" desc="Numbers of Pkgs-1" datatype="int" size="6" type="M"></field> 189 <field desc="Numbers Of Pkgs-1" datatype="string" size="2" type="M"></field> 190 <field desc="Packages DES-1" datatype="string" size="35" type="C"></field> 191 <field fieldname="KGS" desc="Cargo Gross Weight" datatype="decimal" size="9" type="M" format="0.0"></field> 192 <field fieldname="CBM" desc="Cargo Measurement" datatype="decimal" size="9" type="M" format="0.000"></field> 193 <field fieldname="PCS" desc="Numbers Of Pkgs-2" datatype="int" size="6" type="C"></field> 194 </fields> 195 </record> 196 197 <record no="44" desc="MARKS Nos." commandtype="Text" mc="C" sqltext="select Shipping_Marks from ShippingOrder where Shipping_No=dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)" max="1"> 198 <params> 199 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 200 </params> 201 <fields> 202 <field desc="Record ID" datatype="int" size="2" type="M">44</field> 203 <field fieldname="Shipping_Marks" desc="MARKS(1-10)" datatype="string" size="35" type="M" paragraph="10"></field> 204 </fields> 205 </record> 206 207 <record no="47" desc="CARGO description." commandtype="Text" mc="C" sqltext="select Commodity from carrierbookingInfo where booking_no=@Booking_No" max="1"> 208 <params> 209 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 210 </params> 211 <fields> 212 <field desc="Record ID" datatype="int" size="2" type="M">47</field> 213 <field fieldname="Commodity" desc="Commodity" datatype="string" size="35" type="M" paragraph="5"></field> 214 </fields> 215 </record> 216 217 <record no="47" desc="CARGO description." commandtype="Text" mc="C" sqltext="select Cargo_Description from ShippingOrder where Shipping_No=dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)" max="1"> 218 <params> 219 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 220 </params> 221 <fields> 222 <field desc="Record ID" datatype="int" size="2" type="M">47</field> 223 <field fieldname="Cargo_Description" desc="CARGO DESCRIPTION(1-5)" datatype="string" size="35" type="M" paragraph="5"></field> 224 </fields> 225 </record> 226 227 <record no="02" desc="Booking Information" commandtype="Text" mc="C" 228 sqltext="select @CarrierSeqNo Booking_No, 229 replace(A.Shipping_NO,'-','') Number_No, 230 dbo.Edi_GetMainCarrierForPRE(@Booking_No,1) Carrier_SO, 231 A.Carrier_SO Master_Carrier_SO, 232 (select S_FLD_1 from scode where scd_type=0 and Scd_Id = Service) AS Service_Type, 233 (select S_FLD_1 from scode where scd_type=1 and Scd_Id = Shipment_Type) AS Shipment_Type, 234 RTRIM(LTRIM(Carrier)) AS Carrier_Id,RTRIM(LTRIM(Carrier)) Carrier,(select Scv_Code from dbo.CustomerInfo where Cust_Type=2 and Cust_Code=Contratual_Party) Contratual_Party_Code, 235 (select MLog_ContractName from CustomerInfo where Cust_Type = 2 and Cust_Code=Consignee) Contratual_Party,Contract_No, 236 (select S_FLD_1 from scode where scd_type=23 and Scd_Id = Freight_Terms) Freight_Terms,Freight_Terms_At 237 from ShippingOrder A left join carrierbookingInfo B on B.Booking_No=A.Booking_No where A.Booking_No=@Booking_No 238 and A.Shipping_NO != dbo.Edi_GetMainCarrierForPRE(@Booking_No,0)"> 239 <params> 240 <param name="@CarrierSeqNo" datatype="string" size="10" type="input"></param> 241 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 242 </params> 243 <fields> 244 <field desc="Record ID" datatype="int" size="2" type="M">02</field> 245 <field fieldname="Booking_No" desc="Booking NO" datatype="string" size="32" type="M" ></field> 246 <field fieldname="Number_No" desc="SUB BOOKING NO" datatype="string" size="32" type="M" ></field> 247 <field fieldname="Carrier_SO" desc="B/L NO" datatype="string" size="20" type="C" ></field> 248 <field fieldname="Master_Carrier_SO" desc="Master B/L NO" datatype="string" size="20" type="C" ></field> 249 <field fieldname="Service_Type" desc="Delivery term" datatype="string" size="9" type="M" ></field> 250 <field fieldname="Shipment_Type" desc="Shipment Type" datatype="string" size="10" type="M" ></field> 251 <field desc="Booking Party" datatype="string" size="35" type="C" ></field> 252 <field fieldname="Carrier_Id" desc="Issue Party Code" datatype="string" size="13" type="M" ></field> 253 <field fieldname="Carrier" desc="Issue Party" datatype="string" size="35" type="M" ></field> 254 <field fieldname="Contratual_Party_Code" desc="Applicant" datatype="string" size="35" type="C" ></field> 255 <field fieldname="Contratual_Party" desc="Applicant" datatype="string" size="13" type="C" ></field> 256 <field fieldname="Contract_No" desc="Service Contract No." datatype="string" size="15" type="C" ></field> 257 <field fieldname="Freight_Terms" desc="PREPAID OR COLLECT" datatype="string" size="1" type="C" ></field> 258 <field fieldname="Freight_Terms_At" desc="PAYABLE AT (E)" datatype="string" size="5" type="C" ></field> 259 </fields> 260 </record> 261 262 <record no="48" desc="Container of Booking" commandtype="Text" sqltext="select (select S_FLD_1 from scode where scd_Type = 2 and Scd_Id =A.Container_Type) Container_Type, 263 Request_Qty,dbo.Edi_GetAdditional_Service(A.Container_No) Additional_Service,dbo.Edi_GetRequest_No(A.Container_No) Request_No from ContainerInfo A where booking_no=@Booking_No"> 264 <params> 265 <param name="@Booking_No" datatype="string" size="36" type="input"></param> 266 </params> 267 <fields> 268 <field desc="Record ID" datatype="int" size="2" type="M">48</field> 269 <field fieldname="Container_Type" desc="CTN. SIZE TYPE" datatype="string" size="5" type="M"></field> 270 <field fieldname="Request_Qty" desc="CTN. NUMBERS" datatype="int" size="6" type="M"></field> 271 <field desc="CTN. STATUS" datatype="string" size="1" type="C"></field> 272 <field desc="MASTER LCL NO." datatype="string" size="20" type="C"></field> 273 <field fieldname="Additional_Service" desc="ADDITIONAL SERVICE" datatype="string" size="512" type="C"></field> 274 <field fieldname="Request_No" desc="REQUEST NO." datatype="string" size="35" type="C"></field> 275 </fields> 276 </record> 277 278 279 <record no="99" desc="Trailer record" commandtype="Text" sqltext="" > 280 <fields> 281 <field desc="Record ID" datatype="int" size="2" type="M">99</field> 282 <field desc="Record Total of File" datatype="int" size="6" type="M">[rowcount]</field> 283 </fields> 284 </record> 285 </template>