Add XML attribute in mssql using stored procedure

Question

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

It looks like part of your SP is missing - can you show the whole thing? Also, what does your data look like? — CompanyDroneFromSector7G
– CompanyDroneFromSector7G, Commented Apr 25, 2016 at 11:27
@bukko SP is complete..my data look like <CustomerName> <Customer> <Name>xyz</Name> </Customer> <Customer> <Name>Abc</Name> </Customer> </CustomerName> — Karan
– Karan, Commented Apr 25, 2016 at 11:32
SP's begin with CREATE PROCEDURE. Also, I meant your input data i.e. tables, not your output XML. — CompanyDroneFromSector7G
– CompanyDroneFromSector7G, Commented Apr 25, 2016 at 11:34
@bukko Create Procedure GetXML ID BIGINT AS BEGIN SELECT '' AS [CustomerName], (SELECT Name[Customer/Name] FROM Customer where CusId=ID FOR XML PATH(''), TYPE) AS[CustomerName/Customer] FOR XML PATH('') END This is full SP — Karan
– Karan, Commented Apr 25, 2016 at 11:35

Gottfried Lesigang · Accepted Answer · 2016-04-25 11:36:49Z

1

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>

answered Apr 25, 2016 at 11:36

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2016-04-25 11:37:11Z

1

SELECT [@id] = t.id, [Name] = t.name
FROM (
    VALUES (1, 'name'), (2, 'name2')
) t (id, name)
FOR XML PATH('Customer'), ROOT('CustomerName')

answered Apr 25, 2016 at 11:37

Devart

120k23 gold badges172 silver badges189 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

Ha, gotcha! Next time you'll win again :-)

Collectives™ on Stack Overflow

Add XML attribute in mssql using stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related