Need help parsing xml to sql - 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

Transact-SQL (2008)

Need help parsing xml to sql

Author

Topic

spidey0915
Starting Member

4 Posts

Posted - 2011-03-08 : 15:38:01

I'm trying to parse xml below.

<Well> <WbType><OLD>'DRLG'</OLD><NEW>'DEV'</NEW></WbType>
<WbApiNumber><OLD>'42-211- -'</OLD><NEW>'42-211- - '</NEW></WbApiNumber>
</Well>

Can anyone help? I'm trying to get the output to look like this.
Type OLD NEW

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-08 : 17:16:05

Something like this? I assumed you want the node name as the first column

declare @xmlvar xml;

set @xmlvar = '<Well> <WbType><OLD>''DRLG''</OLD><NEW>''DEV''</NEW></WbType>
<WbApiNumber><OLD>''42-211- -''</OLD><NEW>''42-211- - ''</NEW></WbApiNumber>
</Well>';

select 
   xmlnode.value('fn:local-name(.[1])', 'varchar(max)') as [Type],
   xmlnode.query('./NEW/text()') as [New],
   xmlnode.query('./OLD/text()') as [Old]
from 
   @xmlvar.nodes(N'//Well/*') tbl(xmlnode)

spidey0915
Starting Member

4 Posts

Posted - 2011-03-09 : 08:49:04

Thanks!

spidey0915
Starting Member

4 Posts

Posted - 2011-03-09 : 08:55:15

if my xml data is from a table, how do i get that into the "set xmlvar"?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-09 : 09:14:53

SET @xmlvar = (select <columnwithxml> from yourTable where <uniquelydefined>)

Jim

Everyday I learn something that somebody else already knew

spidey0915
Starting Member

4 Posts

Posted - 2011-03-09 : 11:35:33

I got this message when i try to run it.

Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-09 : 11:40:03

Then you didn't uniquely identify the record in the table. You haven't provided any DDL, so you'll have to determine how the where clause needs to be written so that it returns only one record.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 11:46:24

Your where clause has to be such that it returns exactly one row. See Jim's where clause:

where <uniquelydefined>.

If you are trying to apply this to all the rows or some subset of the rows, change your query to do it on each row like this:

select 
   xmlnode.value('fn:local-name(.[1])', 'varchar(max)') as [Type],
   xmlnode.query('./NEW/text()') as [New],
   xmlnode.query('./OLD/text()') as [Old]
from
   YourTable
   cross apply yourXMLColumName.nodes(N'//Well/*') tbl(xmlnode)
where
   -- put the where clause here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources