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

General SQL Server Forums

New to SQL Server Programming

XML

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-08-07 : 06:35:37

Hi,

I want to list all the ID in the xml. but when I try to read that
I am just getting null.

I need the output as

ID
--
1
2
3

---I tried with this one.

declare @l_xml xml,
@l_h INT
set @l_xml = '<ClientGroup>
<ID>1</ID> <ID>2</ID> <ID>3</ID> </ClientGroup>'

EXEC SP_XML_PREPAREDOCUMENT @l_h OUTPUT, @l_xml
select * from openxml(@l_h,'/ClientGroup/ID ',2)
with
(
ID INT
)
EXEC SP_XML_REMOVEDOCUMENT @l_h

Karthik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 06:43:52

Are you using SQL Server 2000 or SQL Server 2005/2008?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 06:53:00

Oh, never mind...

DECLARE	@xml XML,
	@h INT 

SET	@xml = '<ClientGroup>
			<ID>1</ID>
			<ID>22</ID>
			<ID>333</ID>
		</ClientGroup>'

-- SQL Server 2000
EXEC sp_xml_preparedocument @h OUTPUT, @xml

SELECT	*
FROM	OPENXML(@h, '/ClientGroup/ID', 2)
WITH	(
		ID INT '.'
	) 

EXEC sp_xml_removedocument @h

-- SQL Server 2005/2008
SELECT	n.value('.', 'INT') AS ID
FROM	@xml.nodes('/ClientGroup/ID') AS g(n)

N 56°04'39.26"
E 12°55'05.63"

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-08-07 : 07:00:45

Thanks you very much peso I am Using SQL 2005.

It worked. thank you once again

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources