Adding XML attribute and value

• Chaitanya Patil

  Say Hey Kid

  Points: 697

  More actions

  May 26, 2011 at 11:13 pm

  #240366

  <Response>

  <Header ResponseId="017224878" ParticipentId="RXHUB" ReceiverId="T00000000021041" ResponseDate="20110523" ResponseTime="1006">

  <Source PBMName="RXHUBPBM" PBMId="T00000000001000">

  <Receiver ProviderId="4321012352" LastName="Jones" FirstName="Mark" Suffix="MD" Address="" City="" State="" PostalCode="" Country="" CountrySubDivisionCode="">

  <Subscriber SubscriberId="B000000%111111110%001" LastName="PALTROW" FirstName="BRUCE" MiddleName="K" Address="2645 MULBERRY LANE" City="TOLEDO" State="OH" PostalCode="54360" Country="US" Gender="Male" DateOfBirth="19450201" Relatioship="Self">

  <DateTimePeriod Code="" Description=""/>

  <Reference Name="Identification Card Number" Value="111111110" />

  <Reference Name="Family Unit Number" Value="001" />

  <Eligibility>

  <Eligibility Code="1" Description="Active Coverage" HealthPlanName="HEALTH PLAN NAME" ServiceTypeCode="30">

  <Reference Name="Group Number" Value="B000000" Description="HONEYWELL" />

  <Reference Name="Plan Number" Value="PLAN ID" />

  <Reference Name="Drug Formulary Number" Value="FSL101" />

  <Reference Name="Alternative List ID" Value="ALT101" />

  <DateTimePeriod Code="291" Description="Plan" />

  </Eligibility>

  <Eligibility Code="1" Description="Active Coverage" ServiceTypeCode="88">

  <Reference Name="Plan Number 2" Value="PLAN ID 2" />

  <Reference Name="Drug Formulary Number 2" Value="FSL106" />

  <DateTimePeriod Code="" />

  </Eligibility>

  <Eligibility Code="1" Description="Active Coverage" ServiceTypeCode="90">

  <DateTimePeriod Code="" />

  </Eligibility>

  </Eligibility>

  </Subscriber>

  </Receiver>

  </Source>

  <Source PBMName="RXHUBPBM" PBMId="T00000000001000">

  <Receiver ProviderId="4321012352" LastName="Jones" FirstName="Mark" Suffix="MD" Address="" City="" State="" PostalCode="" Country="" CountrySubDivisionCode="">

  <Subscriber SubscriberId="B000000%111111110%001" LastName="PALTROW" FirstName="BRUCE" MiddleName="K" Address="2645 MULBERRY LANE" City="TOLEDO" State="OH" PostalCode="54360" Country="US" Gender="Male" DateOfBirth="19450201" Relatioship="Self">

  <DateTimePeriod Code="" Description=""/>

  <Reference Name="Identification Card Number" Value="111111110" />

  <Reference Name="Family Unit Number" Value="001" />

  <Eligibility>

  <Eligibility Code="1" Description="Active Coverage" HealthPlanName="HEALTH PLAN 123" ServiceTypeCode="30">

  <Reference Name="Group Number" Value="B000025" Description="HONEY" />

  <Reference Name="Plan Number" Value="PLAN ID" />

  <Reference Name="Drug Formulary Number" Value="FSL101" />

  <Reference Name="Alternative List ID" Value="ALT101" />

  <DateTimePeriod Code="291" Description="Plan" />

  </Eligibility>

  <Eligibility Code="1" Description="Active Coverage" HealthPlanName="HEAL123" ServiceTypeCode="88">

  <Reference Name="Plan Number 2" Value="PLAN ID 2" />

  <Reference Name="Drug Formulary Number 2" Value="FSL106" />

  <DateTimePeriod Code="" />

  </Eligibility>

  <Eligibility Code="1" Description="Active Coverage" ServiceTypeCode="90">

  <DateTimePeriod Code="" />

  </Eligibility>

  </Eligibility>

  </Subscriber>

  </Receiver>

  </Source>

  </Header>

  </Response>

  I want to add SrcId attribute to each source node with incrementing SrcId, I tried this, but getting error

  "XML well-formedness check: Duplicate attribute 'SrcId'. Rewrite your XQuery so it returns well-formed XML". Can anyone help me out...

  DECLARE @LV_ID INT=1

  DECLARE @LV_ID_MAX INT=5

  WHILE @LV_ID<=@LV_ID_MAX

  BEGIN

  SET @LV_XML.modify('

  insert attribute SrcId {sql:variable("@LV_ID") }

  into (/Response/Header/Source)[1] ');

  SET @LV_ID=@LV_ID+1

  END

  SELECT @LV_XML;

  GO