Edit XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Edit XML

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-09 : 04:26:05

Hi,

I have an XML file and it has 2 nodes and many columns.
I need to read two columns and based on the values, I need to update those values.

Example.
<Pan>213465789</Pan>
<AccNo>546789</AccNo>

I need to update Pan value as
Pan value/right(accno,3)

Output- <Pan>213465789/789</Pan>

So this task has to be done with out importing the file into the table.

Kindly let me know the steps to perform.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 05:17:19

use modify() method to do that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 05:23:25

see an illustration below


declare @x table
(
t xml
)
insert @x
select'<Root><Pan>213465789</Pan>
<AccNo>546789</AccNo>
</Root>'

UPDATE t1
SET t.modify('replace value of (/Root/Pan/text())[1] with  sql:column("NewPan") ')
FROM
(
SELECT t,m.n.value('Pan[1]','varchar(20)')+'/' + right(m.n.value('AccNo[1]','varchar(20)'),3) as NewPan 
FROM @x
cross apply t.nodes('/Root')m(n)
)t1

SELECT * FROM @x

output
----------------------------
<Root>
  <Pan>213465789/789</Pan>
  <AccNo>546789</AccNo>
</Root>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-09 : 05:55:47

How can we do this using SSIS. The source is XML and the destination is XMX..??

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 05:58:38

quote:
Originally posted by sandesh.ravi

How can we do this using SSIS. The source is XML and the destination is XMX..??

Thanks,
Sandesh

XMX? whats that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-09 : 06:25:18

sorry.. Typo mistake.. its XML..

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 08:33:36

quote:
Originally posted by sandesh.ravi

sorry.. Typo mistake.. its XML..

Thanks,
Sandesh

if both source and destination is XML, why do you need SSIS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2011-12-09 : 09:21:22

The file would have thousands of records. So parse through each record and then update the values in the same file. So which approach would be faster?

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 10:14:35

quote:
Originally posted by sandesh.ravi

The file would have thousands of records. So parse through each record and then update the values in the same file. So which approach would be faster?

Thanks,
Sandesh

still the set based approach based on update will perform better

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpsingh
Starting Member

1 Post

Posted - 2011-12-21 : 07:19:09

Hello,

I am new member in this forum posting site.

Dharm Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 11:57:55

quote:
Originally posted by dpsingh

Hello,

I am new member in this forum posting site.

Dharm Singh

you're welcome to SQLTeam
but please dont hijack other threads
post as a new thread if you've any question

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources