Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Virtual columns and exporting to XML file

Hello everyone!

I am doing a project where I am going through my company's employee database and then extracting the people who have birthdays for the next week. When I have done that, I want to export that information into an XML file, to be read by a web-based flash applet. The SQL code will be used to create a stored procedure and run on a daily basis as part of a job.

The basic XML format should be as follows:

<birthdate id="01" date="10/12/2004">
              <person tid="01">
            <name>Person A</name>
               <url>http://www.wgb</url>
      </person>
      <person tid="02">
            <name>Person B</name>
               <url>http://www.wgb</url>
      </person>
</birthdate>
<birthdate id="02" date="10/13/2004">
</birthdate>

Thank you in advance for your expert help!
Taarik.
0
TZRick
Asked:
TZRick
  • 28
  • 14
  • 9
3 Solutions
 
TZRickAuthor Commented:
The relevant columns in the database are:

TableName: CurrentEmployees
ColumnNames: CE_Salutation
                       CE_FirstName
                       CE_LastName
                       CE_URL

Thank you.
Taarik.
0
 
HilaireCommented:
Unless I'm wrong there should be a birthdate too.

Getting the employee data is not that hard, but to generate the incremental [id]s and [tid]s you'd need to build a temporary table. Are the ids required ?
Couldn't you use employeeID instead ?
0
 
TZRickAuthor Commented:
Well, those were basically the instructions I got from above, so I'm dealing with it.

The birthdate for each person would not be listed by itself...at least not now. (Some people don't like to reveal their age)

The ids for birthdate and person are required for now.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HilaireCommented:
>>Unless I'm wrong there should be a birthdate too<<
I meant in the source data / revelant columns

>>The ids for birthdate and person are required for now.<<
OK
0
 
HilaireCommented:
This works for me, please give it a try
I assumed the employee table had a CE_Birthdate column,
cause I needed a date to display
<birthdate id="01" date="10/12/2004">
                                      ------------

select distinct 1 as tag, 0 as parent, datepart(dw, CE_Birthdate) as [Birthdate!1!id], convert(varchar(10),CE_Birthdate, 101) as [Birthdate!1!date],
null as [Person!2!tid], null as [Person!2!name!element], null as [Person!2!url!element]
from CurrentEmployees
where datepart(week, CE_Birthdate) = datepart(week, getdate())
union all
select 2 as tag, 1 as parent, datepart(dw, CE_Birthdate) , convert(varchar(10),CE_Birthdate, 101), tid, FullName , CE_URL
from (
      select a.CE_Birthdate, a.CE_FirstName + ' ' + a.CE_LastName as FullName, a.CE_URL, count(*) as tid
      from CurrentEmployees a
      inner join CurrentEmployees b on a.CE_Birthdate = b.CE_Birthdate and a.CE_FirstName + ' ' + a.CE_LastName >= b.CE_FirstName + ' ' + b.CE_LastName
      where datepart(week, b.CE_Birthdate) = datepart(week, getdate())
      and datepart(week, b.CE_Birthdate) = datepart(week, getdate())
      group by a.CE_Birthdate, a.CE_FirstName + ' ' + a.CE_LastName , a.CE_URL
) derived
order by [Birthdate!1!id], [Birthdate!1!date], [Person!2!tid]
for xml explicit



0
 
TZRickAuthor Commented:
Thank you very much! I am quite barely intermediate in SQL, so I doubt I would have been able to come up with such a solution!

Is it now possible for me to save the output to an XML file? I have been doing some searching and it seems to be a complex process. Is there hope?

Thanks!
Taarik.
0
 
TZRickAuthor Commented:
BTW...The Column for DateofBirth is CE_DOB. :-)
0
 
TZRickAuthor Commented:
Let me clarify the situation a bit:

The flash app will be displaying the birthdays of people in the organization that fall on today, tomorrow and five more days ahead. So the year of birth will not matter, just the month and day. For example, if I were born today 30 years ago, then my name would be selected. If a friend was born on 10-14-2004, his name will be listed on that day along with all the other people who were born on 10-14 (US format...).

Thanks again!
0
 
TZRickAuthor Commented:
The results so far are:

<Birthdate id="1" date="10/09/1949">
      <Person tid="1">
            <name>Mary</name>
      </Person>
</Birthdate>
<Birthdate id="1" date="10/10/1965">
      <Person tid="1">
            <name>Monique</name>
      </Person>
</Birthdate>
<Birthdate id="1" date="10/13/1946">
      <Person tid="1">
            <name
--------------------------------------------
      (End of record 1)


10/16/1980">
      <Person tid="1">
            <name>Kanson</name>
      </Person>
</Birthdate>
<Birthdate id="5" date="10/17/1946">
      <Person tid="1">
            <name>Eric</name>
      </Person>
</Birthdate>
<Birthdate id="5" date="10/17/1957">
      <Person tid="1">
            <name>Dagogo</name>
      </Per

--------------------------------------------
      (End of record 2)
0
 
HilaireCommented:
>>Is it now possible for me to save the output to an XML file? I have been doing some searching and it seems to be a complex process. Is there hope?<<

Saving from QA will fail miserabily since it splits the output in 2033 characters chunks.
Copying resultset lines to notepad / saving the file will mess things up with unwanted CRLF Characters.
In fact any method using ODBC will split the output in 2033 characters chunks.

If you use VB/VBS/ASP,
you must use an OLEDB connection string,
you can create and ADODB.Stream object,
then Set it as the "Output Stream" property of an ADODB.Command object,
execute the command (SQL Statement above),
and use the SaveToFile method of the stream object to save ot a file.

You'll find plenty of code on the net, but I can provide some if need be.

As a side note, to be a valid "wel-formed" xml file, you'd need a unique  opening and closing tag.

0
 
TZRickAuthor Commented:
Ouch. That's the thing... This SQL code will be placed in a stored procedure and called on a daily basis automatically. I am not using anything other than SQL to generate the output, so there really is no front end to this. Is there no possible way to send output directly to an XML file?

Check out this link: http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04g1.asp
0
 
HilaireCommented:
I've recently developped a stored procedure that generates a XML file from a FOR XML query

Please give it a try ...

create procedure usp_GenerateXMLFileFromQuery(@outputFile varchar(255), @ForXMLQuery varchar(8000)) as
DECLARE @returnCode INT,
      @objStream INT,
      @objCommand INT,
      @errorSource VARCHAR(2550),
      @errorDescription VARCHAR(2550),
      @returnVal INT,
      @objproperties int,
      @obj int,
      @connectionString VARCHAR(1000)
SET @connectionString = 'Data Source='+@@SERVERNAME+'; Provider=SQLOLEDB; Initial Catalog=' + db_name() + '; Integrated Security=SSPI'
EXEC @returnCode = sp_OACreate 'ADODB.Stream', @objStream OUTPUT
EXEC @returnCode = sp_OACreate 'ADODB.Command', @objCommand OUTPUT
EXEC @returnCode = sp_OASetProperty @objCommand, 'ActiveConnection', @connectionString
EXEC @returnCode = sp_OASetProperty @objCommand, 'CommandText', @ForXMLQuery
EXEC @returnCode = sp_OAMethod @objStream,'Open'
--Get a pointer to the properties collection
EXEC @returnCode = sp_OAGetProperty @objCommand, 'Properties', @objproperties out
-- Get a pointer to the "Output Stream" property
EXEC @returnCode = sp_OAMethod @objproperties, 'Item', @obj out, 'Output Stream'
-- Set Property value
EXEC @returnCode = sp_OASetProperty @obj, 'Value', @objStream
-- Invoke execute method
EXEC sp_OAMethod @objCommand, 'Execute', null, null, null, 1024
-- Save output to a file
EXEC @returnCode = sp_OAMethod @objStream,'SaveToFile', null, @outputFile
IF @returnCode <> 0
BEGIN
     EXEC sp_OAGetErrorInfo @objStream, @errorSource OUTPUT, @errorDescription OUTPUT
     SELECT 'SaveToFile Error: ', @errorSource, @errorDescription
END
-- Clean up.
EXEC @returnCode = sp_OADestroy @objStream
EXEC @returnCode = sp_OADestroy @objCommand
EXEC @returnCode = sp_OADestroy @objProperties
EXEC @returnCode = sp_OADestroy @obj
go

-- test code
exec usp_GenerateXMLFileFromQuery 'c:\temp\test2.xml', 'select top 10 country_code, country_name from ref_country FOR XML AUTO, elements'
0
 
TZRickAuthor Commented:
I'm getting an execute error dealing with permissions. I guess I don't have the necessary permissions.
0
 
HilaireCommented:
can you execute the following ?

declare @obj int
exec sp_OACreate 'Scripting.Dictionnary', @obj OUTPUT
exec sp_OADestroy @obj

if not, you could ask execute permissions on sp_OA* procedures to your local dba ...
0
 
TZRickAuthor Commented:
I don't have the permission. I have to talk to my dba then. I'll keep you informed...

Thank you for your help so far!
0
 
apirniaCommented:
Let me begin buy saying that I only read the Question and non of the proposed answers.

What I Undrestood is that you want the data in XML format.


Lets assume that you have a table with all employee info. To export them in a XML format this is what you write your select statement and at the end add the either For XML AUTO or For XML RAW.



Select * from YourTable Where .................  For XML AUTO



Above query will return the data with XML tags. It uses the field names to create the tags.
other than XML Auto there are different commands that you can use.

You can try them in query analyzer and see what XML out put you like to work with.
The other command is "For XML RAW"

Now you have a recordset with thhis XML info in it. In your ASP you write the recordset into a File and then transform that XML file with a XSL file.


If you want more detail on that I can Provide that as well.

I hope this helps....




0
 
TZRickAuthor Commented:
I'm not sure what is going on, but I'm getting an error when I execute the stored procedure:

SaveToFile Error:
ADODB.Stream      Write to file failed.      
0
 
apirniaCommented:
I think you are writing the XML to file from your SP.

Try executing your SP from an ASP page. It is much easier to deal with XML on your ASP apge.


Here is a sample asp page that opens the XML and transform with an XSL:

******************************************************************

strsql = "EXEC Sp_ProcName"


rs.open strsql, conn        


FullTimeDate = Date()
strFileName = FullTimeDate & ".xml"
Set fs = CreateObject("Scripting.FileSystemObject")
Set xmlFile = fs.CreateTextFile(server.MapPath("/XML_Dump/") & "\" & strFileName,TRUE)


'*****************************************************************
'after opening the file we write the first XML tag
xmlfile.write ("<ROOT>")

'We move to the first recordset and print it to the XML file untill the RS is empty
WHILE NOT RS.EOF
      xmlfile.write RS(0)
      RS.MoveNext ()
WEND

xmlfile.write ("</ROOT>")
xmlFile.Close
Set fs=Nothing

Set RS = RS.NextRecordset
loop
 
'*************In here we load the XSL Pages with the XML*********

DIM vUser
vUser = "/XML_Dump/" & strFileName
Response.Write "<INPUT TYPE=HIDDEN VALUE=" & vUser & " NAME=USERFILE></INPUT>"
'****************************************************************
%>

<script LANGUAGE="VBSCRIPT">
'Open the XML file here
SET xmlDoc = CreateObject("MICROSOFT.XMLDOM")
xmlDoc.async = false
xmlDoc.load(USERFILE.value)

'Open the XSL file here
SET Format1XSL = CreateObject("MICROSOFT.XMLDOM")
Format1XSL.async = false
Format1XSL.validateOnParse=false
Format1XSL.resolveExternals=false
Format1XSL.preserveWhiteSpace=false
Format1XSL.load("/TimePunch/TimePunchReport.xsl")



SUB FORMAT1_ONCLICK
      xslTarget.innerHTML = xmlDoc.transformNode(Format1XSL)
END SUB


SUB WINDOW_ONLOAD
      CALL FORMAT1_ONCLICK
END SUB



'*******************************************************************************
</script>


<div id="xslTarget"></div>


 
0
 
TZRickAuthor Commented:
Thank you very much for your input. I am trying to create a Stored Procedure that will run with a scheduled SQL job. Is it possible for me to use ASP to do that?
0
 
apirniaCommented:
You can create the Stored Procedure and execute it with the variables through a schaduled Job
Very simple.... you can look it up in books online.


On the command box you type   "EXEC SP_storedprocname"
0
 
HilaireCommented:
>>SaveToFile Error:
ADODB.Stream     Write to file failed.     <<

If your SQL Server uses windows authentication, the SP I posted above is executed with the credentials of the windows Users that executes the procedure.

If you use SQL Server authentication (eg connect with sqlweblogin/sqlwebpassword),
the code is executed with the credentials of the account that runs the SQL Server service.

In any case you should try to give write permissions to everyone on the target directory (where you want to save the xml file). Then if it works, you'll change permissions to fit your corporate security rules.

Another thing is that the filepath you want to save to must be visible from the SQL Server.
If you need to write to a directory on the WEB server (or are sql and web servers one and the same ??), you need to use a physical path instead of a a 'virtual directory' path, which does not means anything to the SQL server. Again, you'll have to give write permissions accordingly.

HTH

Hilaire

0
 
TZRickAuthor Commented:
Okay. I got the permissions and everything is great. I am using the following stored procedure to save the query results as an XML file:

CREATE PROCEDURE sp_XMLFileOutput

AS

EXEC sp_makewebtask @outputfile = '\\web4\d$\\webs\wwdev\xmlTest\myxmlfile.xml',
@query = 'exec sp_temp',
@templatefile = '\\web4\d$\\webs\wwdev\xmlTest\template.tpl'
GO
0
 
TZRickAuthor Commented:
The file template.tpl has contents as follows:

<root>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</root>
0
 
TZRickAuthor Commented:
I have a quick question...

Today is 10/13/2004 and I am seeing birthdays for 10-9-2004. Does your code return the birthdays for the week beginning on Sunday? Or does it return three days before, today and three days after? Ideally, it should return the birthdays from three days before, today and three days after to make up the seven days.

Thank you once again. Your code is really ingenius!
0
 
HilaireCommented:
>>Does your code return the birthdays for the week beginning on Sunday? <<
the first day of the week is region-dependant.
see @@datefirst documenttation in BOL

if
select @@datefirst    
 returns 1 then it relies on current week , sunday beeing the first day of the week

if you need 3 days after and before, the code can easily be amended accordingly.
Back in a few mins ...

0
 
TZRickAuthor Commented:
Thank you! I am still trying to understand how your code works! Amazing!
0
 
TZRickAuthor Commented:
One thing:

SELECT DISTINCT
      1 AS TAG,
      0 AS PARENT,
      DATEPART(dw, CE_DOB) AS [Birthdate!1!id],            -- Gets # for day of week
      CONVERT(VARCHAR (6), CE_DOB, 101) + CONVERT (VARCHAR (4), DATEPART (YEAR, GETDATE()), 101)  AS [Birthdate!1!date],
      null AS [Person!2!tid],
      null AS [Person!2!name!element]

doesn't work, but the following does:

SELECT DISTINCT
      1 AS TAG,
      0 AS PARENT,
      DATEPART(dw, CE_DOB) AS [Birthdate!1!id],            -- Gets # for day of week
      CONVERT(VARCHAR (10), CE_DOB, 101) AS [Birthdate!1!date],
      null AS [Person!2!tid],
      null AS [Person!2!name!element]
0
 
HilaireCommented:

CONVERT (VARCHAR (4), DATEPART (YEAR, GETDATE()), 101)
could write
DATENAME (YEAR, GETDATE())
0
 
TZRickAuthor Commented:
I get this error after executing sp_XMLFileOutput (listed above) for the variation that does not work. For the variation that does work, the XML file is produced properly by sp_XMLFileOutput.

Server: Msg 6833, Level 11, State 1, Line 0
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
Server: Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 125
SQL Web Assistant: Could not execute the SQL statement.

0
 
HilaireCommented:
see correct syntax in my last post,
and note that this syntax must be used in both select statements
0
 
HilaireCommented:
This version of the SQL part hides year of birth and uses current year instead

select distinct 1 as tag, 0 as parent,
datediff(day, getdate(), convert(datetime,convert(varchar(6),CE_DOB, 101) + datename(year, getdate()), 101))+4 as [Birthdate!1!id],
convert(varchar(6),CE_DOB, 101) + datename(year, getdate())  as [Birthdate!1!date],
null as [Person!2!tid], null as [Person!2!name!element], null as [Person!2!url!element]
from CurrentEmployees
where CE_DOB between dateadd(day, -3, convert(datetime,convert(varchar(6),getdate(), 101) + datename(year, CE_DOB), 101))
and dateadd(day, 3, convert(datetime,convert(varchar(6),getdate(), 101) + datename(year, CE_DOB), 101))
union all
select 2, 1, datediff(day, getdate(), convert(datetime, DOB, 101)) + 4, DOB , tid, FullName , CE_URL
from (
     select convert(datetime,convert(varchar(6),a.CE_DOB, 101) + datename(year, getdate()), 101) as DOB,
     a.CE_FirstName + ' ' + a.CE_LastName as FullName, a.CE_URL, count(*) as tid
     from CurrentEmployees a
     inner join CurrentEmployees b on convert(varchar(6),a.CE_DOB, 101) = convert(varchar(6),b.CE_DOB, 101) and a.CE_FirstName + ' ' + a.CE_LastName >= b.CE_FirstName + ' ' + b.CE_LastName
     where a.CE_DOB between dateadd(day, -3, convert(datetime,convert(varchar(6),getdate(), 101) + datename(year, a.CE_DOB), 101))
     and dateadd(day, 3, convert(datetime,convert(varchar(6),getdate(), 101) + datename(year, a.CE_DOB), 101))
     group by convert(datetime,convert(varchar(6),a.CE_DOB, 101) + datename(year, getdate()), 101),
     a.CE_FirstName + ' ' + a.CE_LastName , a.CE_URL
) derived
order by [Birthdate!1!id], [Birthdate!1!date], [Person!2!tid]
for xml explicit
0
 
TZRickAuthor Commented:
I tried the replacement and it gives the same error. That's strange...
0
 
TZRickAuthor Commented:
Forget the last post...
0
 
TZRickAuthor Commented:
Almost everything works! Amazing stuff! Small glitch: The birthdate 'date' is being displayed as: 2004-10-10T00:00:00
0
 
apirniaCommented:
Try this:

Convert(varchar(10), birthdate , 101) AS birthdate
0
 
TZRickAuthor Commented:
I tried converting "datediff(day, getdate(), convert(datetime,convert(varchar(6),CE_DOB, 101) + datename(year, getdate()), 101))+4" to varchar, but I get the same problem.
0
 
TZRickAuthor Commented:
Actually, that statement was for the ID. Hillaire's solution for the date included:
convert(varchar(6),CE_DOB, 101) + datename(year, getdate())  as [Birthdate!1!date]

I'm not understanding how that is not returning the correct date format.
0
 
apirniaCommented:
You have to do the date add first and then apply the Convert to the final result
0
 
apirniaCommented:
Try this:

convert(varchar(6),CE_DOB + datename(year, getdate()),101) as [Birthdate!1!date]
0
 
apirniaCommented:
If you want the full date you should use varchar(10) instead of varchar(6),
0
 
TZRickAuthor Commented:
By the way...CE_DOB is a date in nvarchar format. I tried your suggestion and that placed about six empty birthdate tags before each valid tag.


e.g. <Birthdate id="1" date="1928-10-10T00:00:00"/>
<Birthdate id="1" date="1944-10-10T00:00:00"/>
<Birthdate id="1" date="1945-10-10T00:00:00"/>
<Birthdate id="1" date="1947-10-10T00:00:00"/>
.
.
.
0
 
apirniaCommented:
Then we have to Convert CE_DOB to a date format then add  datename(year, getdate()),101) to it and then wrap the whole thing in convert(varchar(6),    , 101)
0
 
apirniaCommented:
here we go:

convert(varchar(6), Convert(datetime,CE_DOB,120) + datename(year, getdate()),101) as [Birthdate!1!date]
0
 
TZRickAuthor Commented:
On a last note, the advice from Hilaire on actually creating the XML file had a limitation: After 2033 characters or so, SQL encounters a size limitation.

See: http://www.sqlxml.org/faqs.aspx?faq=29
Also:  http://support.microsoft.com/default.aspx?scid=KB;en-us;q275583

I used the solution found here: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9336&FORUM_ID=5&CAT_ID=3&Topic_Title=SQL+Server+2000+XML&Forum_Title=Developer

The final stored procedure looks like:

CREATE PROCEDURE sp_XMLFileOutput

AS

EXEC master..xp_cmdshell 'bcp "exec sp_GetBirthdays" queryout \\web4\d$\Webs\wwdev\xmltest\temp.xml -Sweb4 -Udirectory -Pdirectorypass -c -r -t'
EXEC master..xp_cmdshell 'copy /B \\web4\d$\Webs\wwdev\xmltest\rootopen.tpl+\\web4\d$\Webs\wwdev\xmltest\temp.xml+\\web4\d$\Webs\wwdev\xmltest\rootcls.tpl \\web4\d$\Webs\wwdev\xmltest\birthdays.xml'


GO

Sincerely,
Taarik.
0
 
HilaireCommented:
@Taarik
the usp_GenerateXMLFileFromQuery stored procedure I gave above uses an OLEDB provider and not ODBC. Only ODBC and Query Analyser (that uses ODBC) split in 2033 characters chunks, as I had mentioned sooner in that thread.
Did you try it ?

using xp_cmdshell and bcp queryout is OK too but you have to hardcode the connection string.

Regards

Hilaire

0
 
TZRickAuthor Commented:
That is true. I forgot about that stored procedure. You are right. Point taken.

Taarik.
0
 
TZRickAuthor Commented:
Hello again Hilaire!

For some strange reason, the last solution stopped working this week, even though it worked last week. So I'm trying to use your stored procedure, but I'm getting a SaveToFile error. Is this the correct syntax:

exec usp_GenerateXMLFileFromQuery 'c:\temp\test2.xml', 'exec sp_GetBirthdays, elements'

What is 'elements'?

Thanks!

Taarik.
0
 
HilaireCommented:
the ',elements' was an option of the 'SELECT ... FOR XML' statement used as an exemple

try
exec usp_GenerateXMLFileFromQuery 'c:\temp\test2.xml', 'exec sp_GetBirthdays'
0
 
TZRickAuthor Commented:
Thanks again Hilaire. I'm getting the same error. I think there is something wrong with the DB/Server. I also had a file creation error with the previous method.

Have a great day!

Taarik.
0
 
HilaireCommented:
Check disk space and permissions on the target directory.
0
 
TZRickAuthor Commented:
There are no problems with permissions and space that we can detect. We even went in and gave full permissions to everyone to try, but it doesn't work. The bcp command works from the command-line, but not from the server. Well, I think we're going to run bcp from a batch file instead of the database. It's so weird though. It worked superbly before and now it's giving errors.

Oh well...that's life and THIS is IT! :-)

Taarik.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 28
  • 14
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now