Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove root from a xml file Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 3:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:30 PM
Points: 1, Visits: 13
I have created a table, created a stored procedure to retrieve data into xml(I used 'for xml path'),
created a package in sql 2005 BIDs, created a execute sql task and a script task. I was able to produce the xml file from SSIS. The output is shown below.

<ROOT>
- <row>
<Module>C</Module>
<AccountRef>1759</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>2734</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>3859</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>40000666</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
</ROOT>

My question is that how can make change </ROOT> to be </xml> as shown below?

<xml>
- <row>
<Module>C</Module>
<AccountRef>1759</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>2734</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>3859</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
- <row>
<Module>C</Module>
<AccountRef>40000666</AccountRef>
<BenefitSuppressionIndicator>BP</BenefitSuppressionIndicator>
<RecoveryInhibitIndicator>RI</RecoveryInhibitIndicator>
</row>
</Xml>
Post #1451493
Posted Friday, May 10, 2013 5:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 2:17 PM
Points: 1,827, Visits: 3,508
Did you look up the syntax for FOR XML - it's right there - option to change the root name. http://msdn.microsoft.com/en-us/library/ms190922(v=sql.90).aspx


Steve.
Post #1451538
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse