reading xml values in sql server

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)

reading xml values in sql server

Author

Topic

laptop545
Starting Member

11 Posts

Posted - 2010-03-18 : 17:29:35

Hello All,
I have a stored procedure which returns an xml. Now, i am writing another database script using this SP.

Now in my script, First i will execute the SP and i am getting the O/P as

<root><person><id>168369</id><fullname>kishopre,ravi</fullname></person><person><id>168374</id><fullname>lasstname12,firstname12</fullname></person></root>

and now i want to get all the id values, may be in some array , so that i need to run another query against all the id values

( say select * from P_PERSON where id = id value in xml )

can any 1 please tell me how to grep all the id values in xml and run the query

Thanks...

Sachin.Nand

2937 Posts

Posted - 2010-03-19 : 02:28:42

O/P u are getting as string or a resultset?

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-03-19 : 02:37:46

declare @x xml
set @x='<root><person><id>168369</id><fullname>kishopre,ravi</fullname></person><person><id>168374</id><fullname>lasstname12,firstname12</fullname></person></root>'
select @x
select x.person.value('.','varchar(40)') from @x.nodes('//id')x(person)

PBUH

laptop545
Starting Member

11 Posts

Posted - 2010-03-19 : 10:20:10

Hello ,
Tnx for the reply...as we are using select i got the values as

168369
and 168374

Now how should i run a query like

select * from P_PERSONS where id = Every value above

The above xml has only 2 values, but sometimes the script may generate many values , say to 150. SO i want to run the query for all 150 values .

can you please help me in this too...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:06:51

see this as an example


declare @x xml

declare @person table
(
ID int,
Age int,
Grade varchar(100)
)

INSERT @Person
SELECT 168369,21,'A' UNION ALL
SELECT 168365,20,'A+' UNION ALL
SELECT 168374,18,'A' UNION ALL
SELECT 168381,19,'B' UNION ALL
SELECT 168344,20,'A' UNION ALL
SELECT 168395,15,'A+' 



set @x='<root><person><id>168369</id><fullname>kishopre,ravi</fullname></person><person><id>168374</id><fullname>lasstname12,firstname12</fullname></person></root>'

SELECT p.ID,t.Name,p.Grade
FROM  @Person p
INNER JOIN (SELECT u.v.value('id[1]','int') AS ID,
            u.v.value('fullname[1]','varchar(100)') AS Name
            FROM @x.nodes('/root/person')u(v)
            )t
            ON t.ID =p.id

output
------------------------------------------------
ID	Name	                  Grade
168369	kishopre,ravi	           A
168374	lasstname12,firstname12	   A

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources