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.
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 ClassID357At present it shows the result asClassID3Thanks*/declare @LinesXML varchar(max)set @LinesXML = '<Classes> <ClassID>3</ClassID> <ClassID>5</ClassID> <ClassID>7</ClassID> </Classes>'declare @idoc intEXEC sp_xml_preparedocument @idoc OUTPUT, @LinesXMLselect ClassIDFROM 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 INTEXEC 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 NULLEXEC sp_xml_removedocument @idocbalaganapathy n.Anything you can imagine is real. |
|
|
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 thisDECLARE @LinesXML XMLSET @LinesXML = ' <Classes> <ClassID>3</ClassID> <ClassID>5</ClassID> <ClassID>7</ClassID> </Classes> 'SELECT n.c.value('.', 'INT') AS ClassIDFROM @LinesXml.nodes('/Classes/ClassID') AS n(c) N 56°04'39.26"E 12°55'05.63" |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-01-08 : 08:17:49
|
Thank you |
|
|
|
|
|