Need help to modify XML file using T-SQL

Question

Post reply

Need help to modify XML file using T-SQL

durga.palepu

Hall of Fame

Points: 3530
More actions
July 9, 2016 at 6:23 am

#327741

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.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

no need to cross post...please select one thread and repoint all others to just the one.

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

durga.palepu Hall of Fame Points: 3530 More actions · Answer 2

durga.palepu

Hall of Fame

Points: 3530

July 9, 2016 at 7:24 am

#1889291

How to do that pointing?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

something along the lines of

edit your duplicate posts

delete content and replace with something like this.....

"duplicate thread please post all replies to <insert URL here>" where URL is the single thread you wish to keep

thanks for taking note.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

durga.palepu Hall of Fame Points: 3530 More actions · Answer 4

durga.palepu

Hall of Fame

Points: 3530

July 9, 2016 at 7:39 am

#1889293

Any idea how to resolve my XML requirement?

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

durga.palepu (7/9/2016)
Any idea how to resolve my XML requirement?

To start with... Do you know how to shred XML?

--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)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

durga.palepu (7/9/2016)
How to do that pointing?

seems like Jeff has already "wyafy" 😛

Jeff Moden (7/9/2016)
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.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

durga.palepu Hall of Fame Points: 3530 More actions · Answer 7

@jeff,

I know shredding XML but not an expert, that's why posted here so that I can get some inputs.

I am not expecting spoon feeding but a basic approach is enough.

Jeff, if you are an expert in how to do XML shredding, please provide an approach to solve my requirement.

pietlinden SSC Guru Points: 63503 More actions · Answer 8

See if Alan Burstein or Eirikur Eiriksson will bite... might be Monday though.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

durga.palepu (7/9/2016)
@Jeff,
I know shredding XML but not an expert, that's why posted here so that I can get some inputs.
I am not expecting spoon feeding but a basic approach is enough.
Jeff, if you are an expert in how to do XML shredding, please provide an approach to solve my requirement.

I'm definitely NOT an expert at XML... not even a good casual user of it.

However, if you do know how to shred XML enough, the thing to do would be to shred it into a table or derived table, add any missing pieces you may need to the table or derived table, and then regenerate the XML from that.

--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)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

Here is a quick solution that starts by selecting the DataArea node and then remove the Sync node from the selection, should be enough to get you passed this hurdle.

😎

DECLARE @OUTXML XML;

DECLARE @TXML XML = N'<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>';

-- Since we are only interested in the DataArea then we start by

-- grabbing that part into an XML variable;

SELECT @OUTXML = @TXML.query('SyncContract/DataArea');

-- Then delete the unwanted Sync node

SET @OUTXML.modify('

delete DataArea/Sync

');

SELECT @OUTXML;

Output

<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>

durga.palepu Hall of Fame Points: 3530 More actions · Answer 11

Thank you, that's what exactly I am looking for and its helped me a lot.

I will focus on XML schredding further and learn about it deeper.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 12

durga.palepu (7/10/2016)
Thank you, that's what exactly I am looking for and its helped me a lot.
I will focus on XML schredding further and learn about it deeper.

You are very welcome.

😎