Remove root from a xml file

• mcdbaexpert

  SSC Enthusiast

  Points: 133

  More actions

  May 10, 2013 at 3:42 am

  #277518

  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>

• stevefromOZ

  SSC-Forever

  Points: 43646

  More actions

  May 10, 2013 at 5:48 am

  #1614147

  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.