Help me with a 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)

Help me with a Query !

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-08-20 : 03:03:33

Hi all,

Please help me in writing a query to fetch the values in xml into rows...


declare @text xml
declare @DocHandle Int   
select @text  = '
<Root> 
            <UnsendItems UserId="82345" >
                 <Items ItemCode="ABCI"  ItemId="458963,859632" />
		 <Items ItemCode="RRCH"  ItemId="963147,563142" />
		 <Items ItemCode="TTQW"  ItemId="563289,555631" />
	     </UnsendItems>
</Root>'

I expect the result in the following format...

UserId	ItemCode	ItemId
-----------------------------------------
82345	ABCI		458963,859632
82345	RRCH		963147,563142
82345	TTQW		563289,555631

thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 04:11:58

[code]select
N.Node.value('../@UserId','varchar(50)') AS UserId,
N.Node.value('./@ItemCode','varchar(50)') AS ItemCode,
N.Node.value('./@ItemId','varchar(50)') AS ItemId
from @text.nodes('Root/UnsendItems/Items')N(Node)[/code]

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 02:01:33

I need to do the same job but my xml value is dynamic, that is, my xml contains column name and values of one row as below:

<t><col1 name>col1 value</col1 name><col2 name>col2 value</col2 name></t>

in which the column names and values may differ for each xml value; is there any way to do it in a loop,etc.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 02:20:04

look into booksonline on OPENXML. Perhaps thats what you're looking for.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 02:39:01

Thanks visakh

OPENXML helps me in fetching the column names and values but in case that column names are known in advance. Isn't there a way to use it for unknown column names and number?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 02:44:52

quote:
Originally posted by Peace2007

Thanks visakh

OPENXML helps me in fetching the column names and values but in case that column names are know in advance. Isn't there a way to use it for unknown column names and number?

Why does your column name change dynamically? how are you generating the xml?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 03:06:45

I'm auditing a few tables through service broker and gather their data as an xml typed field into one table in another server.Now I need to read those xmls and fetch the changed column values and apply the changes to the similar tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources