Need help to modify XML file using T-SQL

• durga.palepu

  Hall of Fame

  Points: 3530

  More actions

  July 9, 2016 at 7:13 am

  #100228

  Hi Experts,

  I am using SQL Server 2005 for this requirement.

  I have a requirement to modify XML file to delete few nodes and return result as XML.

  Source XML file structure:

  <SyncContract xmlns:ns2="http://schema.abc.com/ABCOAGIS/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schema.abc.com/ABCOAGIS/2" xsi:noNamespaceSchemaLocation="http://schema.abc.com/ABCOAGIS/2">

  <ns2:ApplicationArea>

  <ns2:Sender>

  <ns2:LogicalID>lid://abc.cm.cm</ns2:LogicalID>

  <ns2:ComponentID>clcm</ns2:ComponentID>

  </ns2:Sender>

  <ns2:CreationDateTime>2016-06-28T14:08:54.707Z</ns2:CreationDateTime>

  <ns2:BODID>infor.cm.cm:Sync.Contract:1467122934707</ns2:BODID>

  </ns2:ApplicationArea>

  <DataArea>

  <Contract>

  <ContractHeader>

  <AlternateDocumentID>

  <ID>Services Work Order</ID>

  </AlternateDocumentID>

  <Description>ABC QA Contract6</Description>

  <DocumentDateTime>2016-06-28T14:08:54.707Z</DocumentDateTime>

  <DocumentID>

  <ID variationId="0">Manual.3000</ID>

  </DocumentID>

  <LastModificationDateTime>2016-06-28T14:08:03.229Z</LastModificationDateTime>

  <Status>

  <Code>INTERNAL_REVIEW</Code>

  </Status>

  <UserArea>

  <ClmUserArea>

  <Agreement id="3000" name="Services Work Order">

  <AgreementValue>8720.0</AgreementValue>

  <Attributes id="18946" name="ReApprovalRequired">NA</Attributes>

  <Attributes id="18945" name="Entity">US0AB</Attributes>

  </Agreement>

  </ClmUserArea>

  </UserArea>

  </ContractHeader>

  <ContractSchedule/>

  </Contract>

  <Sync>

  <AccountingEntityId>ABCMLC_TRN</AccountingEntityId>

  <ActionCriteria>

  <ActionExpression actionCode="Add"/>

  </ActionCriteria>

  <TenantID>ABCMLC_TRN</TenantID>

  </Sync>

  </DataArea>

  </SyncContract>

  Result XML file required after deleting <SyncContract> and <Sync> nodes from source XML is followed:

  <DataArea>

  <Contract>

  <ContractHeader>

  <AlternateDocumentID>

  <ID>Services Work Order</ID>

  </AlternateDocumentID>

  <Description>ABC QA Contract6</Description>

  <DocumentDateTime>2016-06-28T14:08:54.707Z</DocumentDateTime>

  <DocumentID>

  <ID variationId="0">Manual.3000</ID>

  </DocumentID>

  <LastModificationDateTime>2016-06-28T14:08:03.229Z</LastModificationDateTime>

  <Status>

  <Code>INTERNAL_REVIEW</Code>

  </Status>

  <UserArea>

  <ClmUserArea>

  <Agreement id="3000" name="Services Work Order">

  <AgreementValue>8720.0</AgreementValue>

  <Attributes id="18946" name="ReApprovalRequired">NA</Attributes>

  <Attributes id="18945" name="Entity">US0AB</Attributes>

  </Agreement>

  </ClmUserArea>

  </UserArea>

  </ContractHeader>

  <ContractSchedule/>

  </Contract>

  </DataArea>

  Your help is highly appreciated.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  July 9, 2016 at 9:00 am

  #1889296

  Duplicate post. Please, let's keep all the answers in one spot. Don't post on this thread. Go to the thread already accumulating answers here http://www.sqlservercentral.com/Forums/Topic1800658-3412-1.aspx instead.

  Thanks folks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)