getting table with 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

getting table with xml

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 11:10:30

Hi,
I can't get the following xml when i run the stored proc. The output is wrong because first it shows different data and second it repeats the same data twice.

<instances>
<instance>
<instanceid>0</instanceid>
<councilname>xxxxx</councilname>
<connectionstring>xxxxx</connectionstring>
</instance>

<instance>
<instanceid>0</instanceid>
<councilname>yyy</councilname>
<connectionstring>yyyy</connectionstring>
</instance>

</instances>

code snippet of stored procedure


EXEC sp_xml_preparedocument @idoc OUTPUT, 
			@InstancesXml,
			'<instancesxml xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>'

			SELECT   
				InstanceId,
				CouncilName, 
				ConnectionString 
			FROM OPENXML(@idoc,'/instances/instance')  WITH
			(	
				InstanceId INT './instanceid[not(@xsi:nil = "true")]',
				CouncilName nvarchar './councilname',
				ConnectionString nvarchar './connectionstring'
			)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 11:18:55

are you trying to generate xml or do you need to extract values from xml? code looks like you're doing latter

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-06 : 11:30:19

found the answer....i should have defined nvar(255) in both fields - i was only getting 1 letter in both cases which so happened to be the same letter A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 11:32:46

Oh I see

Glad that you sorted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources