[HELP] FOR XML

Question

Post reply

[HELP] FOR XML

knockyo

SSC Enthusiast

Points: 165
More actions
July 22, 2013 at 4:16 am

#395176

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>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Dana Medley SSCertifiable Points: 6764 More actions · Answer 1

Maybe this will help.

SELECT 1234 AS '@TranID', 'ABC123' AS '@OrderNum',

'MY' AS 'Current_Location',

(

SELECT 'OK' AS '@Code', 'FINE' AS '@Reason_Code', 'KUU1234' AS 'Remark'

FOR XML PATH('Status'), TYPE

) AS 'node()',

'A5555' AS 'Consignment_Number',

(

SELECT '0' AS '@Consignment_Number', '0' AS '@Box_Qty'

FOR XML PATH('Total_Shipped'), TYPE

) AS 'node()'

FOR XML PATH('Track_N_Trace')

Everything is awesome!

knockyo SSC Enthusiast Points: 165 More actions · Answer 2

Dana Medley (7/22/2013)
Maybe this will help.
SELECT 1234 AS '@TranID', 'ABC123' AS '@OrderNum',
'MY' AS 'Current_Location',
(
SELECT 'OK' AS '@Code', 'FINE' AS '@Reason_Code', 'KUU1234' AS 'Remark'
FOR XML PATH('Status'), TYPE
) AS 'node()',
'A5555' AS 'Consignment_Number',
(
SELECT '0' AS '@Consignment_Number', '0' AS '@Box_Qty'
FOR XML PATH('Total_Shipped'), TYPE
) AS 'node()'
FOR XML PATH('Track_N_Trace')

Hi,

Thanks your help. It really helping me .. 🙂