Error while Displaying a string containing xml

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)

Error while Displaying a string containing xml

Author

Topic

willfindavid
Starting Member

27 Posts

Posted - 2009-01-28 : 01:18:46

I use the following code,


SELECT @strXml = '<AccessRights projectId="'+CAST(@ProjectID AS VARCHAR(100))+'" userId="'+CAST(@UserID AS VARCHAR(100))+'">'    
SELECT @strXml = @strXml+'<object Id="' +  CAST(scr.objectCode as varchar(100))    
 + '" value="' +     
CASE WHEN AccessRight = 1 THEN  'N'        
WHEN AccessRight = 2 then 'V'        
WHEN AccessRight = 3 then 'F'        
ELSE 'V' 
END+'"></object>'    
   
FROM #screens scr left join #access acc 
ON acc.objectCode = scr.objectCode
SELECT @strXml = @strXml+'</AccessRights>' 

SELECT @strXml AS ResultSet

When i use the select for displaying the xml string it shows,

An error occurred while executing batch. Error message is: Invalid calling sequence: file stream must be initialized first.

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-28 : 01:32:03

Declare @strXml First and then assign the SELECT statement to @strXml

Avantha Siriwardana

willfindavid
Starting Member

27 Posts

Posted - 2009-01-28 : 01:39:10

i have declared @strXml as,

DECLARE @strXml varchar(max)

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-28 : 01:45:19

Declare it as DECLARE @strXml TEXT

Avantha Siriwardana

willfindavid
Starting Member

27 Posts

Posted - 2009-01-28 : 02:07:31

An Error occurs if @strXml is declared as text,

The text, ntext, and image data types are invalid for local variables.

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-28 : 02:45:48

but where is your xml path??

Avantha Siriwardana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 02:46:41

Why are you using the technique to assemble an XML string?

SET	@strXml = '<AccessRights projectId="' + CAST(@ProjectID AS VARCHAR(100)) + '" userId="' + CAST(@UserID AS VARCHAR(100)) + '">'

SELECT		@strXml = @strXml + '<object Id="' + CAST(scr.objectCode AS VARCHAR(100))
			+ '" value="'
			+ CASE acc.AccessRight
				WHEN 1 THEN 'N'
				WHEN 3 then 'F'
				ELSE 'V' 
			END
			+ '"></object>'
FROM		#Screens AS scr
LEFT JOIN	#Access AS acc ON acc.objectCode = scr.objectCode

SET	@strXml = @strXml + '</AccessRights>'

SELECT	@strXml AS ResultSet

Why are you not using the built-in XML support?

See http://weblogs.sqlteam.com/peterl/archive/2009/01/20/Create-another-nested-XML-hierarchy.aspx
and http://weblogs.sqlteam.com/peterl/archive/2008/11/06/Create-nested-hierachy-XML.aspx

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

willfindavid
Starting Member

27 Posts

Posted - 2009-01-28 : 04:15:05

Can anyone tell how to generate xml like,

<AccessRights projectId="4296" userId="12345">
<object Id="270" value="V" />
<object Id="156" value="V" />
<object Id="157" value="V" />
<object Id="158" value="V" />
<object Id="159" value="V" />
<object Id="160" value="V" />
<object Id="161" value="V" />
<object Id="162" value="V" />
<object Id="163" value="V" />
<object Id="164" value="V" />
<object Id="165" value="V" />
<object Id="54" value="V" />
</AccessRights>

with projectId and userId provided as input parameters
and object id and value retrived from the tables #Screens and #Access using left join where #Access.objectCode = #Screens.objectCode

willfindavid
Starting Member

27 Posts

Posted - 2009-01-28 : 04:18:37

I have tried like this but cannot able to display the attributes projectId and userId for the root node

SELECT object.objectCode 'Id',
CASE WHEN AccessRight = 1 THEN 'N'
WHEN AccessRight = 2 then 'V'
WHEN AccessRight = 3 then 'F'
ELSE 'V'
END 'value'
FROM #screens object
left join
#access acc
ON acc.objectCode = object.objectCode
FOR XML AUTO,ROOT('AccessRights')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 06:52:54

[code]DECLARE @ProjectID INT,
@UserID INT

SELECT @ProjectID = 4296,
@UserID = 12345

DECLARE @Sample TABLE
(
ObjectCode INT,
AccessRight INT
)

INSERT @Sample
(
ObjectCode,
AccessRight
)
SELECT 270, 2 UNION ALL
SELECT 270, 4 UNION ALL
SELECT 156, 5 UNION ALL
SELECT 157, 2 UNION ALL
SELECT 158, 4 UNION ALL
SELECT 159, 5 UNION ALL
SELECT 160, 2 UNION ALL
SELECT 161, 4 UNION ALL
SELECT 162, 5 UNION ALL
SELECT 163, 2 UNION ALL
SELECT 164, 4 UNION ALL
SELECT 165, 5 UNION ALL
SELECT 54, 2

DECLARE @x XML

SET @x = (
SELECT ObjectCode AS [@Id],
CASE AccessRight
WHEN 1 THEN 'N'
WHEN 3 then 'F'
ELSE 'V'
END AS [@value]
FROM @Sample
FOR XML PATH('object')
)

set @x = '<AccessRights projectID="' + CAST(@ProjectID AS VARCHAR(11))
+ '" userID="' + CAST(@UserID AS VARCHAR(11))
+ '">'
+ CAST(@x AS VARCHAR(MAX))
+ '</AccessRights>'

SELECT @x[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources