FOR XML problems - 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

SQL Server 2000 Forums

SQL Server Development (2000)

FOR XML problems

Author

Topic

stomply
Starting Member

2 Posts

Posted - 2002-03-13 : 18:01:35

I have a problem where I am executing a stored procedure to generate XML from a database. I can get the XML in the SQL Query Analyzer, but when I use ASP to try and retrieve the XML fragment so I can modify it the XML string is not processed correctly as a string.

Here's the code,

-- SQL stored proc
SELECT 1 AS Tag,
NULL AS Parent,
ShowName AS [Show!1!Name],
NULL AS [Performance!2!],
NULL AS [Performance!2!Venue!element],
NULL AS [Performance!2!Date!element],
NULL AS [Performance!2!Time!element]
FROM Show
UNION ALL
SELECT 2,
1,
S.ShowName,
NULL,
VenueName,
DayMonthYear,
TimeSlot
FROM Show S
INNER JOIN ShowDateTimeVenue ON S.ShowID=ShowDateTimeVenue.ShowID
INNER JOIN TimeSlot ON ShowDateTimeVenue.TimeSlotID=TimeSlot.TimeSlotID
INNER JOIN DateSlot ON ShowDateTimeVenue.DateSlotID=DateSlot.DateSlotID
INNER JOIN Venue ON ShowDateTimeVenue.VenueID=Venue.VenueID
ORDER BY [Show!1!Name],[Performance!2!Venue!element]
FOR XML EXPLICIT

this produces XML in the form of,

<Show Name="Hamlet">
<Performance>
<Venue>Town Hall</Venue>
<Date>01/02/02</Date>
<Time>11:00-1:00pm</Time>
</Performance>
<Performance>
<Venue>Town Hall</Venue>
<Date>01/02/02</Date>
<Time>7:00-9:30pm</Time>
</Performance>
</Show>

But when I try and response.write this out in ASP the browser displays this,

?NameD?Venue?Date?Time?Show?Performance?A???????L???

Has anyone got any ideas? I assume its a datatype problem and the SQL command FOR XML produces a string of a different datatype to ASP but I can't find a workaround. Help please.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-13 : 18:10:20

You need to use the ADOD.Command object in association with a ADODB.Stream object.

If you need code, just ask...

HTH

DavidM

Tomorrow is the same day as Today was the day before.

stomply
Starting Member

2 Posts

Posted - 2002-03-13 : 18:39:43

I'm not quite sure how I would utilise the ADODB.Stream object in this instance.
At present I'm accessing the recordset using the the coloumn reference, i.e. strXML = gvobjRS(0).
Any help would be appreciated.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-13 : 19:12:55

Stomply,

Here is some vb code.....


Dim pStream As New Stream
Dim pcmd As Command
Set pcmd = New Command

pStream.Open

With pcmd
    .CommandText = SP
    .CommandType = adCmdStoredProc
    
   'Bind Parameters
    ConvertArrayToParameter cmd, Params()
    
    'Do you want to transform the XML here?
    If LenB(XSLPath) > 0 Then
        .Properties("Base Path") = App.Path
        .Properties("XSL") = XSLPath
    End If
    
    'Of Course
    OpenConnection
    
    .ActiveConnection = mCNN

    'Give root element name if asked
    If LenB(Root) > 0 Then
        .Properties("XML Root") = "Data"
    End If
    
    'Bind Stream Object to Output
    .Properties("Output Stream") = pStream

    'Execute to the Stream
    .Execute , , adExecuteStream
     Set .ActiveConnection = Nothing
     Set mCNN = Nothing
End With

Now that you have the stream.. call its "ReadText" method..

HTH

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-13 : 19:16:06

Thank you David - just what I was looking for

(guess who's made my day?)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-13 : 19:44:43

rrb,

Notice the line that you can apply the XSL transformation...


'Do you want to transform the XML here?    
If LenB(XSLPath) > 0 Then
   .Properties("Base Path") = App.Path  
   .Properties("XSL") = XSLPath
End If

I had MAJOR arguments with the ASP boys and gals about this one..

I said.. "What do we need you people for? I can render web pages from here."

They said... "You have got be ^%$^%$ kidding?"

Seriously that was there only argument! Be afraid front-end dudes, be very afraid! The DBA's are coming!!!!

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-13 : 20:50:25

If I understand you correctly - then that's pretty damned cool. Have to study up on my XSL and see how many people I can put out of work....

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources