Karan Karan - 7 months ago 15
SQL Question

Add XML attribute in mssql using stored procedure

i want output

<CustomerName>
<Customer id='1'>
<Name>xyz</Name>
</Customer>
<Customer id='2'>
<Name>Abc</Name>
</Customer>
</CustomerName>


for this output i wrote SP

SELECT
'' AS [CustomerName],
(SELECT
Name[Customer/Name]
FOR XML PATH(''), TYPE) AS[CustomerName/Customer]
FOR XML PATH('')


not able to add Id attribute, please help me

Answer

Try this:

DECLARE @tblCust TABLE(id INT, CustomerName VARCHAR(100));
INSERT INTO @tblCust VALUES
 (1,'xyz')
,(2,'Abc');

SELECT id AS [@id]
      ,CustomerName AS [Name]
FROM @tblCust  
FOR XML PATH('Customer'),ROOT('CustomerName')

This is the result

<CustomerName>
  <Customer id="1">
    <Name>xyz</Name>
  </Customer>
  <Customer id="2">
    <Name>Abc</Name>
  </Customer>
</CustomerName>
Comments