SQL XML Query - Automate Sitemap XML Generation
Jan 23, 2007
Hay Gurus'
Please can you help out this with this question????
I have created a SQL 2005 select statement that returns an XML file with the correct items (in the correct format) to be used as a sitemap... This needs to have a function and then a select statement run in conjunction 1 CREATE FUNCTION QuickNav(@MenuItem int)
2 RETURNS XML
3 WITH RETURNS NULL ON NULL INPUT
4 BEGIN RETURN
5 (SELECT ID as "@ID", Title as "@title", url as "@url", Description as "@Description", ParentID as "@ParentID", NodeGroupParent as "@NodeGroupParent", ImageURL as "@ImageURL", NodeGroupSort as "@NodeGroupSort",
6 CASE WHEN NodeGroupParent=@MenuItem
7 THEN dbo.QuickNav(id)
8 END
9 FROM dbo.QuickNavView WHERE NodeGroupParent=@MenuItem
10 ORDER BY NodeGroupSort
11 FOR XML PATH('siteMapNode'), TYPE)
12 END 1 WITH XMLNAMESPACES (
2 DEFAULT 'http://schemas.microsoft.com/AspNet/SiteMap-File-1.0'
3 )
4 SELECT ID as "@ID", Title as "@title", url as "@url", Description as "@Description", ParentID as "@ParentID", NodeGroupParent as "@NodeGroupParent", ImageURL as "@ImageURL", NodeGroupSort as "@NodeGroupSort",
5 CASE WHEN id=31
6 THEN dbo.QuickNav(id)
7 END
8 FROM QuickNavView
9 WHERE id=31
10 ORDER BY NodeGroupSort
11 FOR XML PATH('siteMapNode'), ROOT('siteMap')
So when i run this code in SQL 2005 Express it works fine and i can save the results as a Web.sitemap and my menu looks ok...But now i want to automate this creation process.... have a button that when clicked saves an XML file (Web.sitemap) to a server directory... How Can I Do This????? This may seem like a dumb question but i really do need help and have searched everwhere... any help, pointers to a tutorial would be great...Any Ideas .....Tod BTW the results look like... (and work fine with both tree control and menu) 1 <siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0">
2 <siteMapNode ID="31" title="root" url="~/Default.aspx" ImageURL="~imagesEASLogoSmall.jpg" NodeGroupSort="1">
3 <siteMapNode ID="34" title="Business" Description="Group node for Business Objects" NodeGroupParent="31" xmlns="">
4 <siteMapNode ID="1" title="Business Case" url="~/EASObjectInventory.aspx?LookUPID=BusinessCase" NodeGroupParent="34" ImageURL="~imagesBusinessCase.gif" NodeGroupSort="100" />
5 <siteMapNode ID="19" title="Business Plan" url="~/EASObjectInventory.aspx?LookUPID=BusinessPlan" NodeGroupParent="34" ImageURL="~imagesBusinessPlan.gif" NodeGroupSort="200">
6 <siteMapNode ID="9" title="Mission" url="~/EASObjectInventory.aspx?LookUPID=Mission" ParentID="19" NodeGroupParent="19" ImageURL="~imagesMission.gif" NodeGroupSort="210" />
7 <siteMapNode ID="10" title="Vision" url="~/EASObjectInventory.aspx?LookUPID=Vision" ParentID="9" NodeGroupParent="19" ImageURL="~imagesVision.gif" NodeGroupSort="220" />
8 <siteMapNode ID="11" title="Goal" url="~/EASObjectInventory.aspx?LookUPID=Goal" ParentID="10" NodeGroupParent="19" ImageURL="~imagesGoal.gif" NodeGroupSort="230" />
9 <siteMapNode ID="12" title="Strategic Objective" url="~/EASObjectInventory.aspx?LookUPID=Strategic Objective" ParentID="11" NodeGroupParent="19" ImageURL="~imagesObjective.gif" NodeGroupSort="240" />
10 </siteMapNode>
11 <siteMapNode ID="7" title="Bussiness Concept" url="~/EASObjectInventory.aspx?LookUPID=BusinessConcept" NodeGroupParent="34" ImageURL="~imagesBusinessConcept.gif" NodeGroupSort="300" />
12 </siteMapNode>
13 <siteMapNode ID="24" title="Compliance Item" url="~/EASObjectInventory.aspx?LookUPID=ComplianceItem" NodeGroupParent="31" ImageURL="~imagesComplianceItem.gif" NodeGroupSort="400" xmlns="" />
14 <siteMapNode ID="32" title="Exposure" url="~/EASObjectInventory.aspx?LookUPID=Exposure" NodeGroupParent="31" ImageURL="~imagesRisk.gif" NodeGroupSort="500" xmlns="" />
15 <siteMapNode ID="26" title="Human Resource" url="~/EASObjectInventory.aspx?LookUPID=HumanResource" NodeGroupParent="31" ImageURL="~imagesHumanResources.gif" NodeGroupSort="600" xmlns="" />
16 <siteMapNode ID="13" title="IT Component" url="~/EASObjectInventory.aspx?LookUPID=ITComponent" NodeGroupParent="31" ImageURL="~imagesITComponent.gif" NodeGroupSort="700" xmlns="" />
17 <siteMapNode ID="14" title="IT Hardware" url="~/EASObjectInventory.aspx?LookUPID=ITHardware" NodeGroupParent="31" ImageURL="~imagesITHardware.gif" NodeGroupSort="800" xmlns="" />
18 <siteMapNode ID="16" title="KPI" url="~/EASObjectInventory.aspx?LookUPID=KPI" NodeGroupParent="31" ImageURL="~imagesKPI.gif" NodeGroupSort="900" xmlns="" />
19 <siteMapNode ID="18" title="Portfolio" url="~/EASObjectInventory.aspx?LookUPID=Portfolio" NodeGroupParent="31" ImageURL="~imagesPortfolio.gif" NodeGroupSort="1000" xmlns="" />
20 <siteMapNode ID="27" title="Process" url="~/EASObjectInventory.aspx?LookUPID=Process" NodeGroupParent="31" ImageURL="~imagesProcess.gif" NodeGroupSort="1100" xmlns="" />
21 <siteMapNode ID="20" title="Product" url="~/EASObjectInventory.aspx?LookUPID=Product" NodeGroupParent="31" ImageURL="~imagesProduct.gif" NodeGroupSort="1200" xmlns="" />
22 <siteMapNode ID="29" title="Program" url="~/EASObjectInventory.aspx?LookUPID=Program" NodeGroupParent="31" ImageURL="~imagesProgram.gif" NodeGroupSort="1300" xmlns="" />
23 <siteMapNode ID="17" title="Project" url="~/EASObjectInventory.aspx?LookUPID=Project" NodeGroupParent="31" ImageURL="~imagesProject.gif" NodeGroupSort="1400" xmlns="" />
24 <siteMapNode ID="21" title="Service" url="~/EASObjectInventory.aspx?LookUPID=Service" NodeGroupParent="31" ImageURL="~imagesService.gif" NodeGroupSort="1600" xmlns="" />
25 <siteMapNode ID="25" title="Stakeholder" url="~/EASObjectInventory.aspx?LookUPID=Stakeholder" NodeGroupParent="31" ImageURL="~imagesStakeholder.gif" NodeGroupSort="1700" xmlns="" />
26 <siteMapNode ID="23" title="Standard" url="~/EASObjectInventory.aspx?LookUPID=Standard" NodeGroupParent="31" ImageURL="~imagesStandard.gif" NodeGroupSort="1800" xmlns="" />
27 <siteMapNode ID="35" title="Administration" url="~/admin/Default.aspx" NodeGroupParent="31" NodeGroupSort="5000" xmlns="" />
28 </siteMapNode>
29 </siteMap>
View 4 Replies
ADVERTISEMENT
Apr 6, 2004
hello,
does anybody know how i could automate an offline olap cube, so that it will be updatet daily?
thanks chri
View 3 Replies
View Related
Feb 11, 2007
Dear,
I have designed a SiteMap provider for SQL Server. But I am worried about the notification service of SQL Server. My SiteMap requires service broker enabled in SQL Server. When I have web farm (multiple clustered server) does the Sql Server service broker notify all the web servers? Or, it notifies only the server request?
Have anubody implement such scenario? Looking forward for your reply.
Sincere Regards,Sultan
View 1 Replies
View Related
Mar 4, 2008
I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies
View Related
Oct 12, 2007
Hi All,
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line
WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
and
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, contacts.last_name, p21_view_inventory_return_hdr.buyer_id, p21_view_unvouched_po_currency_report.po_line_number, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.contacts contacts, P21.dbo.p21_view_inventory_return_hdr p21_view_inventory_return_hdr, P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report
WHERE p21_view_inventory_return_hdr.return_number = p21_view_unvouched_po_currency_report.unvouched_document_no AND contacts.id = p21_view_inventory_return_hdr.buyer_id
View 4 Replies
View Related
Jun 18, 2008
Hello All,
I have two columns in MS-SQL, 1st column has Web-Links and 2nd has Dates
Example:
Names
Date
Link1
Date1
Link2
Date2
Link3
Date3
Link4
Date4
Link5
Date5
Link6
Date6
Required Output:
<?xml version="1.0" encoding="UTF-8"?><urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> <loc>http://www.example.com/Link1</loc> <lastmod>2005-01-01</lastmod> <changefreq>monthly</changefreq> <priority>0.8</priority> </url>
etc.....
</urlset>
Thanks.
- David
View 2 Replies
View Related
May 16, 2007
Hi everyone I have a class called SqlSiteMapProvider which extends StaticSiteMapProvider to dynamically create the sitemap data for my site. Although I am getting a problem in that whenever I add a page to the database (via the application) it doesnt' seem to appear in the site map, even though the data is saving to the database .
If I edit the class, save and refresh the app, then the page does appear.
Can anyone think of why this might be? Or know how I can fix it. Is there some kind of application refresh that I need to do or something?
Thanks
Dave
View 15 Replies
View Related
May 17, 2007
Hi I have already posted this yesterday (http://forums.asp.net/p/1111248/1711653.aspx#1711653) but think it might have been lost by the wayside, can anyone help me?
I have a class called SqlSiteMapProvider which extends StaticSiteMapProvider to dynamically create the sitemap data for my site. Although I am getting a problem in that whenever I add a page to the database (via the application) it doesnt' seem to appear in the site map, even though the data is saving to the database .
If I edit the class, save and refresh the app, then the page does appear.
Can anyone think of why this might be? Or know how I can fix it. Is there some kind of application refresh that I need to do or something?
Thanks
Dave
View 1 Replies
View Related
Oct 3, 2000
I have a quick question regarding SQL Server Enterprise Manager. I'm looking at setting up a job to automatically create DDL for a user database. This will be done along with our normal nightly backup routine.
I'm very familiar with using EM to create SQL scripts, but is there anyway to schedule this task? I've considered DTS and some type of scheduled package, but can't seem to find anything similar. I'm thinking I may need a custom task.
Could someone please shed some light on the subject? If not from within EM, how about any third party tools? FYI - I already own the Embarcadero suite and am trying it out wwith that.
Thank You.
Anthony Robinson
View 10 Replies
View Related
Jul 11, 2007
hi
I want to generate excel file which contain table name , column name,datatype ,size
how we can do in sql server
is there any way
pleases tell the steps
View 2 Replies
View Related
Nov 3, 2007
Server:
SQL 2005 SP2 on Win 2003 Ent. SP1
A 3rd part app is requiring that I create a credential, whick in turn requires an SMK be set. When I try to create the credential, I get an error message indicating a decryption error. When I run the alter command to regenerate the key (without force) it throws an error indicating the key cannot be decrypted. According to a KB article I found, this may indicate that a key has never been generated.
My question is, I have a number of production databases in this instance, including SQL Reporting Services. Except for the SRS DB's, all other user db's are simple db's that don't use encryption. If I run the Force command to generate the key, am I going to break anything? I'm really concerned about report servioces.
Thanks.
View 5 Replies
View Related
May 7, 2002
Is it possible to execute a DTS package via a stored procedure?
We have created a web-based application with a SQL back-end, I would like the
user to select an option on the .asp page that will then trigger the DTS.
Is this possible? Any suggestions would be greatly appreciated.
Thanks
View 2 Replies
View Related
Apr 28, 2004
I need a tool to generate sql code of database including all data like "insert into table values()". Same as sql file in IBuySpy portal. How can I generate a file like this? I tried with enterprise manager but it doesn't generate insert statements and default values of some fileds lost.
Can someone help me?
View 1 Replies
View Related
Feb 7, 2005
Hello all,
I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.
The information in the database for the report is as follows
FileName : Test1
FileExtn : True
Path = \ram\C$Reports
Render_Format = PDF
Username = Administrator
Password = password
Writemode = Overwrite
All the fields are varchar(50) in size.
View 1 Replies
View Related
Dec 5, 2007
Hi,
How to create surrogate key in a dimension table?
What transformations can be used to create it?
View 6 Replies
View Related
Sep 13, 2005
Hi all,I am following the procedure where I generate XMLs of tables and put itin a DataSet and read that dataset back into another database to bewritten into the tables.Although I am VERY close to completion a small problem that I am facingis that the XMLs being generated have as the table name "table" and notthe actual names.Is this a known issue or am I amiss? and, does anyone have a solutionfor this?Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Sep 13, 2005
Hi guys,Apologies Simon for not making it clearer last time. What i am doing isreading a table from the SQL Server 2000 database and using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to write to the DataSet. But I realised that when I do that the XML isgenerated correctly, the only error being that<xs:element name="Table"> comes in as the tablename instead of theactual table name.Any suggestions will be appreciated. Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Jul 20, 2005
HelloFor my client, I need to generate reports from the information storedin the database. The client has fixed format forms (on paper e.g. USCustoms forms etc).Will I need to redesign the forms in the application and then show theinformation?Another approach is to scan the forms as image and print theinformation on top of that image, so when it is printed , theinformation will be displayed at the right places.Is there any other way? How is the reporting done if the forms arepre-defined and the information is stored in a databaseThanks for your input
View 1 Replies
View Related
Jan 16, 2006
Hi, I'm trying to use the SK script from Donald Farmers book but the code isn't accepted
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim CurrentKey As Integer
Public Overrides Sub PreExecute()
CurrentKey = CInt(Me.Variables.FILCodesSK)
End Sub
Public Overrides Sub Input_ProcessInputRow(ByVal Row As Input0Buffer)
CurrentKey += 1
Row.SurrogateKey = CurrentKey
End Sub
End Class
There is a problem with the use of the overrides on the Input_ProcessInputRow sub should this be renamed?
Cheers, Al
View 1 Replies
View Related
Feb 14, 2007
Hi,
I am working with SQL Server 2005 Reporting Service from few days, though I am
not expert, for some reason I have to run on field without having sound
knowledge of RDL, but need your help, gys. I am using SQL Server Business
Intelligence Development Studio to design report.
Here is the procedure of my work to populate a RDL report
I used a stored procedure for the DataSet of my RDL
Then I drag and drop necessary field to my report layout.
Put required parameters to preview tab and then run report.
This is quite simple, I didn't face any problem with this process, even though
the process may not correct, but working perfect. My problem is
when the stored procedure returns multiple result set. The data tab only
shows the first result set though the SP returns multiple result set, I have
run the SP in the Query Analyzer. I don't want change my stored procedure.
And please give me some suggestions about the best procedure
to develop RDL report in real life.
Please reply me ASAP, it€™s very urgent.
Thank youTareqe
View 1 Replies
View Related
Sep 19, 2006
Hi,
Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)
View 4 Replies
View Related
Mar 2, 2006
Can someone let me know any 10 things that are usally automated using the SQL Server Agent,
I mean besides backup what all can i automate
Thanks
View 1 Replies
View Related
Jan 28, 2014
I want to create a form (Infopath) or web page where the user can request for a new database I want the user to fill in the required info (like the size, name columns.....) and ones they're done filling the required info, they will click on a button and that should trigger the database automation script to run and if the database with the requested name is not created, the script should create a database for the user.
View 1 Replies
View Related
Mar 2, 2006
Can someone let me know any 10 things that are usally automated using the SQL Server Agent,
I mean besides backup what all can i automate
Thanks
View 10 Replies
View Related
Mar 15, 2007
I want to automate a query that I am running everyday. So, what I want to do is run it at a particular time of the day say 11:00pm. Is this possible? Should I create a stored procedure? And also, I want to run an update statement on a particular table on the first of every month. If anyone can give me some advice on this, I would appreciate it.
Jared
View 8 Replies
View Related
May 7, 2007
Folks:
I have a text file which has all "drop" statements (drop proc, drop view)... I want to run this statements on a daily basis. How can I automate this?
Thanks !
View 8 Replies
View Related
Jul 23, 2005
hello people!I am very new to the SQL World. Currently I work on DB2 and ORACLE andthis is a new "fruit" for me.I have the task to automate the following scenario:- backup database A- restore database A into database B (overwrite database B)- perform the above every night at 23:00.Can anyone indicate me where do i find info how to perform the above?I know that you can do it through the Enterprise Manager but I need toscript the above (probably).Thanks
View 6 Replies
View Related
Aug 14, 2006
Hi All,
I have created the package using Import data ( from comma delimeted text file)
Actually I schedule the JOB to run the package from SQL Server Agent
I am getting error when start the job. ( job started successfully and executing time am getting error.)
here is the Log error
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'VGOSVRSaleem', error code 0x2095. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
If I run the .dtsx file from outside. it works fine
do I need to do any kind of setup before running/starting the job ??
Please advise
Cheers
Saleem
View 3 Replies
View Related
Feb 8, 2008
I am running this procedure to copy the call data info from oma11wicacct01 to oma11csql5tst01 and to vsql4shared
INSERT INTO [oma11csql5tst01].[rms sql].dbo.WICCallData ( ClientID, ProgramID, Calls, ActSeconds, UsageDate )
SELECT WICCallData.ClientID, WICCallData.ProgramID, Sum(WICCallData.Calls) AS SumOfCalls, Sum(WICCallData.ActSeconds) AS SumOfActSeconds, WICCallData.UsageDate
FROM WICCallData
GROUP BY WICCallData.ClientID, WICCallData.ProgramID, WICCallData.UsageDate
HAVING (Sum(WICCallData.Calls)>0 or Sum(WICCallData.ActSeconds)>0) AND WICCallData.UsageDate>'2/3/2008'
The usage date has to be greater than the max date on the target database!
How can I automate the execution?
View 3 Replies
View Related
Dec 19, 2006
hi!
i already have a program that when you click a button it will get the new transactions in the database then pass it to another database now i need to automate the passing... once there is a new entry in the database it should automatically pass it to the other database. how could i do this?!
thanks!
View 5 Replies
View Related
Feb 3, 2005
Does anyone know an efficient method for generating a sequence number in the following form?
Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X
I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1
The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.
Any ideas? Thanks.
View 10 Replies
View Related
Mar 4, 2006
Hi,
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
Thanks
-Sudhakar
View 4 Replies
View Related
May 29, 2012
I want to make up a system that I can create barcodes for my students and scan books for checkout. I am using Windows xp and .net winforms to finish the task. But I have no source for doing that. I am also wondering if my HTC sense could be used for scanning.
View 13 Replies
View Related