locked
sql to xml: element with attribute using 'for xml path()' RRS feed

  • Question

  • Hi all,

    How can get this kind of xml using a select ... for xml path('SUPPLIER').

      <SUPPLIER>
       <SUPPLIER_ID type="supplier_specific">Test</SUPPLIER_ID>
       <SUPPLIER_NAME>Testname</SUPPLIER_NAME>
      </SUPPLIER>

    Guess this might be very easy, but I don't quite get it ...

    Thx for any hints or links

    Reto E.

    Friday, May 14, 2010 1:24 PM

Answers

  • Try this:

    SELECT 
    	'supplier_specific' AS "SUPPLIER_ID/@type",
    	'Test' AS "SUPPLIER_ID/*",
    	'Testname'	"SUPPLIER_NAME"
    FOR XML PATH('SUPPLIER')
    • Proposed as answer by BobChauvin Tuesday, May 18, 2010 8:42 PM
    • Marked as answer by KJian_ Monday, May 24, 2010 6:23 AM
    Friday, May 14, 2010 5:43 PM
    Answerer

All replies

  • Got it myself ...

    select 
    		(select 'supplier_specific'		"@type"
    			 ,(select 'Test' for xml path(''), type)
    		for xml path('SUPPLIER_ID'), type)
    	,'Testname'	"SUPPLIER_NAME"
    for xml path('SUPPLIER')

    I have to use a nested select statment.

    But there is maybe an easier way to do this?

    Reto E.

    Friday, May 14, 2010 1:55 PM
  • Try this:

    SELECT 
    	'supplier_specific' AS "SUPPLIER_ID/@type",
    	'Test' AS "SUPPLIER_ID/*",
    	'Testname'	"SUPPLIER_NAME"
    FOR XML PATH('SUPPLIER')
    • Proposed as answer by BobChauvin Tuesday, May 18, 2010 8:42 PM
    • Marked as answer by KJian_ Monday, May 24, 2010 6:23 AM
    Friday, May 14, 2010 5:43 PM
    Answerer