xml.query - 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 2005 Forums

Transact-SQL (2005)

xml.query

Author

Topic

pokemon
Starting Member

1 Post

Posted - 2011-05-29 : 09:19:21

Hello my xml table is like below which has SNS.xml and I am trying to get columns table by table separetly as table S000 and S001

can you help me how can I do?

<?xml version="1.0" encoding="iso-8859-1" ?>
- <ENTEGRATION DESC="">
- <TABLE NAME="S000">
- <RECORD>
<TESN>0080347957</TESN>
<SIPN>AB453543534</SIPN>
<TUR>001</TUR>
<SORG>3700</SORG>
<DKAN>21</DKAN>
<TRH>20110518</TRH>
</RECORD>
</TABLE>
- <TABLE NAME="S001">
- <RECORD>
<E1SKOD>99</E1SKOD>
<E1TESL>0080347957</E1TESL>
<E1KALN>900001</E1KALN>
<E1UKAL>NA45353</E1UKAL>
<E1FRDP>0001</E1FRDP>
<E1MURU>AA00S</E1MURU>
</RECORD>
</TABLE>
</ENTEGRATION>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-30 : 11:34:53

You can do it as shown below, this example shows getting data from the S000 table:


declare @x xml;
set @x = 'Your XML here';

select
   c.value('(./TESN)[1]','varchar(100)') as TESN,
   c.value('(./SIPN)[1]','varchar(100)') as SIPN
from
   @x.nodes('//TABLE[@NAME="S000"]/RECORD') T(c);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources