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)
 openxml

Author  Topic 

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-08 : 03:56:52
/*using the sql below, how is it possible to show the results as

ClassID
3
5
7

At present it shows the result as
ClassID
3

Thanks
*/
declare @LinesXML varchar(max)
set @LinesXML =
'<Classes>
<ClassID>3</ClassID>
<ClassID>5</ClassID>
<ClassID>7</ClassID>
</Classes>'

declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @LinesXML

select
ClassID
FROM OPENXML (@idoc, '/Classes',2)
WITH (
ClassID int
)

EXEC sp_xml_removedocument @idoc

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-08 : 04:22:52
Please try this.

DECLARE @LinesXML VARCHAR(MAX)
SET @LinesXML = '<Classes><ClassID>3</ClassID><ClassID>5</ClassID><ClassID>7</ClassID></Classes>'

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @LinesXML

--SELECT ClassID FROM OPENXML (@idoc, '/Classes',2)
--WITH (ClassID INT)

SELECT [text]
FROM OPENXML (@idoc, '/Classes/ClassID')
WHERE [text] IS NOT NULL

EXEC sp_xml_removedocument @idoc



balaganapathy n.

Anything you can imagine is real.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-08 : 05:03:23
Since you are using SQL Server 2005, get away from the old XML manipulating syntax.
Use this
DECLARE @LinesXML XML

SET @LinesXML = '
<Classes>
<ClassID>3</ClassID>
<ClassID>5</ClassID>
<ClassID>7</ClassID>
</Classes>
'

SELECT n.c.value('.', 'INT') AS ClassID
FROM @LinesXml.nodes('/Classes/ClassID') AS n(c)



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-08 : 08:17:49
Thank you
Go to Top of Page
   

- Advertisement -