Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
knockyo
Yak Posting Veteran
83 Posts |
Posted - 2013-07-22 : 02:34:50
|
Hi All,I need use SQL (for xml) to write in this customize format of XML as below:<track_n_trace tranid="1234" ordernum="ABC123"> <current_location>MY</current_location> <status code="OK" reason_code="FINE"> <remark>KUU1234</remark> </status> <consignment_number>A5555</consignment_number> <total_shipped overall_qty="0" box_qty="0"/></track_n_trace> Please advice. :) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 02:40:19
|
WHere do you get values form? do they come from a table or you pass it through variables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
knockyo
Yak Posting Veteran
83 Posts |
Posted - 2013-07-22 : 02:45:25
|
quote: Originally posted by visakh16 WHere do you get values form? do they come from a table or you pass it through variables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Hi it came from a table, i able get those values from different table. But i cant generate well the format what I need... :( ... please help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-22 : 02:46:46
|
show us the table structure with some sample data and also XML format you want out of it. Otherwise we wont be able to guess out your table columnnames!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
knockyo
Yak Posting Veteran
83 Posts |
Posted - 2013-07-22 : 04:03:21
|
quote: Originally posted by visakh16 show us the table structure with some sample data and also XML format you want out of it. Otherwise we wont be able to guess out your table columnnames!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Hi,Below is my code:select tranid, ordernum,(SELECT a.statusFROM datafeedactionlog a (nolock)WHERE a.object_uid = track_n_trace.uidFOR XML AUTO,TYPE)from tnt AS track_n_tracewheretrack_n_trace.ordernum in ('ABC123')FOR XML AUTO But i only able generate the format as this:<track_n_trace tranid="1234" ordernum="ABC123> <a status="DONE " /></track_n_trace> |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 06:39:00
|
[code]DECLARE @Sample TABLE ( TranID SMALLINT NOT NULL, OrderNum CHAR(6) NOT NULL, Location CHAR(2) NOT NULL, StatusCode CHAR(2) NOT NULL, ReasonCode CHAR(4) NOT NULL, Remark VARCHAR(8) NOT NULL, Number CHAR(5) NOT NULL, OverallQty TINYINT NOT NULL, BoxQty TINYINT NOT NULL );INSERT @SampleVALUES (1234, 'ABC123', 'MY', 'OK', 'FINE', 'KUU1234', 'A5555', 0, 0);SELECT TranID AS [@tranid], OrderNum AS [@ordernum], Location AS current_location, StatusCode AS [status/@code], ReasonCode AS [status/@reason_code], Remark AS [status/remark], Number AS [consignment_number], OverallQty AS [total_shipped/@overall_qty], BoxQty AS [total_shipped/@box_qty]FROM @SampleFOR XML PATH('track_n_trace');[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|