Flatten out an XML Document (Currently using OPENXML)

I am attempting to take a column with an XML datatype and make it available for reporting with as little code as possible. Specifically, we are storing credit report info in a column that has an XML datatype. We are using the OPENXML command to navigate the XML structure:

DECLARE @.idoc int
DECLARE @.doc xml
SET @.doc = 'GET THE XML COLUMN HERE
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *
FROM OPENXML (@.idoc, '/XML_INTERFACE/CREDITREPORT/OBJECTS/DBCOMMCREDITSCORE',2)
WITH (
Rating varchar(10) 'DBRATING',
OverFlow xml '@.mp:xmltext')
EXEC sp_xml_removedocument @.idoc

The result will be a column that shows the Rating field and an Overflow column that shows the XML string.

I can take the above code and put it in a stored procedure to return the values in a flat format. My questions:

Does anyone know how I can use a view to display the information? (Create View cannot have the above statements. Is there a way to create a tabular representation off of the SP result set, etc.)
Does anyone have a better way of taking an XML datatype and making this available for report writers?

Dan O

1. The XQuery "nodes()" function will work for this. It is similar to OPENXML, but it is just used from within a normal SELECT statement, so you could create views on top of it.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

2. nodes() seems like the way to go, but you could also try pre-shredding the xml into a more convenient relational structure if your XML has a lot of nesting that would require many joins to reassemble.

Flat File Records Dropped During Import

Hello,
I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

Thank you for your help!

cdun2I may have found the problem. The first record does not contain a full set of characters, and when I set up the fixed field column positions originally, I was not able to define the columns for the full string. Only the first one third of the row characters need to be imported, so I ignored this issue.

I have moved a single full length record to the top of the flat file, and the text file properties box now can 'see' the full string. I'll include the rest of the columns (which are not imported) and see if that works.|||No, that didn't work. I moved a record containing all characters to the top of the file, redefined the columns based on the full string, changed the column mappings, and reconfigured the transformations. When I did the import, the destination SQL Server table received even fewer records.

What can I do about this problem?

Thanks again.

cdun2|||If this is a one time only situation, why don't you just import the flat file into Access 2003 and then import the Access table to SQL server?|||What are you using to import the data? DTS? BCP?sql

First time writing, string manipulation?

I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.

For Example:
"GOLF IS FUN,BOWLING IS GREAT"

What Id like to get as my results:

"Golf is fun, Bowling is great"

Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi

Welcome to the forum :D

By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase

I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.

Anyhoo - see how you get on.

HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))

set nocount on

While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))

Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO

Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.

what sql is good at is set based operations.

FireEvent issues with Reporting Services

I am attempting to use the following command line (and associated script) to trigger an existing subscription within Reporting Services (2000):
command line:

rs.exe -i MyScriptFile.rss -s "MyReportServer/ReportServer"

The input scriptfile (MyScriptFile) contains the lines below where the scheduleID is taken from Reporting Services from an existing 'once only' schedule. The schedule, - when triggered - should fire off a subscription and email recipients with a particular existing report.

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

Unfortunately, it doesn't work and I almost always get the response:

Unhandled exception:
The request failed with HTTP status 404: Not Found.

If I comment out the line rs.FireEvent( . . . from the script then the command line runs successfully. I can only conclude that there appears to be something wrong with this particular line.

Any ideas anyone?

Kev

Rs.exe takes care of creating the proxy to SSRS and doing authentication, so the first two lines of code (Dim RS & rs.Credentials) aren't actually necessary. The rest of your code looks fine, though.

You're saying that it sometimes does work?

Here are a few things to try:

- Can you check your Subscription ID to make sure it actually exists?

- Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

- FireEvent is the only method you're actually firing against the SOAP API...why don't you include another before you call FireEvent (something simple like ListChildren()) to make sure that the web service can do *any* work on your behalf.

- I've never seen someone specify the server name in their command-line w/o appending http:// first. This mean it won't work, I've just never seen it. Can you try something like this: rs.exe -i myFile.rss -s http://localhost/reportserver -u myDomain\myAccount -p myPassword ? Do this after you remove the first two lines of your code.

Let us know...good luck!

|||Russell, Thanks tremendously for the reply! I spent two whole days on this (without success) trying every variation I could think of.

Firstly, no the code in the original post has never worked; I've not been able to trigger anything. The Subscription ID definitely exists however: I highlighted it and copied it from within the Reporting Services address bar (in the Scheduling page) directly making sure I had it perfect. I did wonder whether it 'was' the intended ScheduleID but the address bar in the browser reads:

http://MyServer/Reports/Pages/Schedule.aspx?ScheduleID=87206163-7665-4458-a86c-75b84cf18b2d

. . . so I am assuming that I really 'do' indeed have the correct ScheduleID it's looking for (87206163-7665-4458-a86c-75b84cf18b2d)? Or is it? I've tried this with double quotes, without quotes, with single quotes all with no luck.

Secondly, the code below works perfectly indicating that the SOAP API will indeed do something for me:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"

with the following code in the ListFormatExtensions.rss file:

Sub Main
For Each Ext As Extension In rs.ListExtensions(ExtensionTypeEnum.Render)
Console.WriteLine(Ext.Name)
Next
End Sub

Strangely, ListChildren (as you suggested) didn't work at all? It failed with error BC30451: Name 'ListChildren' is not declared. I would assume that this is something perhaps due to the fact that I am using Reporting Services 2000 and not 2005? The actual failed code I used in the rss file was:

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.ListChildren()
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub
The server name also works either way I specify it with the ListFormatExtensions test. So both these code snippets work happily and there's no issue with username and password:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"
rs.exe -i ListFormatExtensions.rss -s http://MyServer/ReportServer

I tried the option you suggested (without the first two lines of code) which it didn't like:

Sub Main
FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

error BC30451: Name 'FireEvent' is not declared

. . . so I promptly put the first two lines back in.

I am still trying to figure out what you mean by - Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

The web address you gave simply takes me straight to the MSDN definition for FireEvent in Reporting Services 2005.

Kev


|||

Hey Kev --

Even though you can remove "Dim RS", etc. you still use "rs.Whatever" in your code

This worked for me:

1. Created a new subscription off the Adventure Works - Company Sales report: Used the fileshare delivery extension because I don't have an SMTP server handy

2. Put it on a schedule that would not fire until 3 days from now

3. Executed select * from reportserver..subscriptions to get the Subscription ID

4. Threw code below in an RSS file:

Sub Main()

Try
rs.FireEvent("TimedSubscription", "E34849EE-63EE-4BF6-957F-55A9B1132DDB")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

5. In Report Manager, made sure that my user had "Generate Events" permissions (a system permission)

6. Executed the code with rs.exe -i c:\tryme.rss -s http://servername/reportserver

7. Checked my fileshare, found the report sitting there.

|||

Yes, make sure that you are using the subscription ID, as Russell is, and not the schedule ID that you said you were using.

-Daniel

|||

Guys, thanks for your input!! The problem is now sorted.

Unfortunately, I never gave a thought to the fact that I was running the rs.exe command on my own desktop and not the server on which Reporting Services is actually installed ! Dumb hey! When I put the file TriggerReport.rss on the report server and then ran the command to execute on the report server itself (using the psexec command):

psexec \\myserver rs.exe -i C:\TriggerReport.rss -s "http://myserver/ReportServer"

. . . it triggered the report immediately. The report promptly arrived in my email. I guess the lesson here is to stand back often and ask yourself if you're doing anything obviously stupid before blaming the code.

Kev

|||What Imports do you need for your .NET app if using SSRS 2005? Just thought I'd ask since you guys are the only place I've found really good info on this subject.