I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA fo
r
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.DaveK wrote:
> I need to store log files that are created in XML format so I can query so
me
> of the fields.
Why not just use an XQuery implementation to query them direct? I see no
requirement here to involve a database at all.
///Peter
> I have only minor knowledge of XML, but have been SQL DBA for
> a while. I just have not had a need to use XML in the SQL environment. W
e
> are using SQL2000. What is the best way to do the following:
> 1. Create a DB for storing the log files? Some variation on the usual way
?
> 2. Import the individual files into the DB. Eventually I can write a DTS
or
> something to import?
> 3. Query the DB. Create some reports on usage.|||After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but th
e
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.|||DaveK wrote:
> After a five minute web search and SQL help search I don't see how I can
> avoid using SQL to build some sort of datbase just so I can address backup
,
> security, etc. It looks like XPath and XQuery can do some searching, but
the
> other items that I need to cover are not really addressed. This is an
> example of one event log item I want to store.
> <CreateDate>7/31/2007</CreateDate>
> <CreateTime>10:19:25</CreateTime>
> <Logger>xxxxd7yrrt11</Logger>
> <Events>
> <Event>
> <EventID>8001</EventID>
> <Description>Login</Description>
> <Category>Audit</Category>
> <Source>LAN Client</Source>
> <SubSource>xxxx_8</SubSource>
> <UserName>test.name</UserName>
> <UserID>347</UserID>
> <Computer>xxx7YRRT11</Computer>
> <Date>07/31/2007</Date>
> <Time>10:19:49</Time>
> <ObjectType>User</ObjectType>
> <Details></Details>
> </Event>
> I have to admit I am not a convert to the XML world, but as I said, I am a
> newbie to it as well. In this case it just seems like a fancy way to
> eliminate delimited importing. The format is not likely to change.
AFAIK all database systems now offer some kind of "Import XML" plugin.
If yours doesn't, you'll need to turn the XML into CSV or whatever your
system consumes. The easiest way to do this is to write an XSLT script,
and I think there are several quoted or linked in Dave Pawson's XSL FAQ
at http://www.dpawson.co.uk/xsl/
XML is just a fancy way of identifying information: you can see from the
above example that it is much clearer in naming items and positioning
them in the hierarchy than (for example) CSV. If the format is stable,
then a little routine to run XSLT over the data and spit out CSV should
do you just fine.
The following appears to work for the sample above (with the addition of
the missing </Events> end-tag and the enclosing root element
<data>...</data> ):
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="text"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<xsl:apply-templates select="data/CreateDate"/>
<xsl:apply-templates select="data/CreateTime"/>
<xsl:apply-templates select="data/Logger"/>
<xsl:apply-templates select="data/Events/Event/*"/>
</xsl:template>
<!-- fields that start a record -->
<xsl:template match="CreateDate|EventID">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
</xsl:template>
<!-- fields that occur in mid-record -->
<xsl:template match="*">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
</xsl:template>
<!-- fields that end a record -->
<xsl:template match="Logger|Details">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"
</xsl:text>
</xsl:template>
</xsl:stylesheet>
$ java -jar /usr/local/saxon/b8.5/saxon8.jar -o test.csv test.xml test.xsl
$ cat test.csv
"7/31/2007,"10:19:25","xxxxd7yrrt11"
"8001,"Login","Audit","LAN
Client","xxxx_8","test.name","347","xxx7YRRT11","07/31/2007","10:19:49","Use
r",""
$
This makes the assumption that your import routine can do something
different with record #1...
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Thank you. This is very helpful. I did not paste in the whole log so misse
d
the ending tags.
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Friday, March 30, 2012
Importing XML for Newbies
I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.
Wednesday, March 28, 2012
Importing text from a flat text file.
I have a DOS scripts that echo's some PC data to a local log file but instea
d
of writing directly to this log file i'd like the output of this script to
populate an SQL 2000 database. How would i go about redirecting the output
from a flat text file to a SQL database.
Regards
John>> How would i go about redirecting the output from a flat text file to a
Some options include Bulk Insert, BCP IN or DTS. Simply search the index in
SQL Server Books Online & you'll find all the information you need to use
them. Also, for DTS, check out www.sqldts.com
Anith|||"ValleyBoy" <ValleyBoy@.discussions.microsoft.com> wrote in message
news:D54A35D3-BE24-458A-B371-27B0A239B043@.microsoft.com...
> I have a DOS scripts that echo's some PC data to a local log file but
instead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Take a look at bcp, BULK INSERT and osql. I'm not sure which approach will
work with your given situation, or if it's even possible to have it
redirected. You may have to dump it to the flat file and then bcp or BULK
INSERT the data in to SQL Server as part of another batch.
Another possible option is to have DTS run the DOS jobs and do something
with the input. I'm not a DTS expert, so I can't say whether or not this
approach is feasible either.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||for this you might check DTS available in BOL
alternatively check for osql command in BOL
eg:
osql /U alma /P /i stores.qry
or u can use bulk Insert
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ValleyBoy" wrote:
> I have a DOS scripts that echo's some PC data to a local log file but inst
ead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
d
of writing directly to this log file i'd like the output of this script to
populate an SQL 2000 database. How would i go about redirecting the output
from a flat text file to a SQL database.
Regards
John>> How would i go about redirecting the output from a flat text file to a
Some options include Bulk Insert, BCP IN or DTS. Simply search the index in
SQL Server Books Online & you'll find all the information you need to use
them. Also, for DTS, check out www.sqldts.com
Anith|||"ValleyBoy" <ValleyBoy@.discussions.microsoft.com> wrote in message
news:D54A35D3-BE24-458A-B371-27B0A239B043@.microsoft.com...
> I have a DOS scripts that echo's some PC data to a local log file but
instead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Take a look at bcp, BULK INSERT and osql. I'm not sure which approach will
work with your given situation, or if it's even possible to have it
redirected. You may have to dump it to the flat file and then bcp or BULK
INSERT the data in to SQL Server as part of another batch.
Another possible option is to have DTS run the DOS jobs and do something
with the input. I'm not a DTS expert, so I can't say whether or not this
approach is feasible either.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||for this you might check DTS available in BOL
alternatively check for osql command in BOL
eg:
osql /U alma /P /i stores.qry
or u can use bulk Insert
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ValleyBoy" wrote:
> I have a DOS scripts that echo's some PC data to a local log file but inst
ead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Monday, March 12, 2012
importing data to MSDE
Hello,
What is the best way to import data to a client's MSDE
database from our website? We want our client to log onto
our website so we can update a table on the client's PC. I
need a little direction on this, not sure which is the best
way to go.
Thank you,
Randers
hi Randers,
Randers wrote:
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
perhaps you could provide an ftp based solution to download new data from
your website and the MSDE remote client proceed with a scheduled job to bulk
insert it..
you can even set the job to notify via mail of the success/failure of it...
(just use an SMTP solution like http://www.sqldev.net/xp/xpsmtp.htm )
or mail them, but this become more manual as the final user has perhaps to
read the mail, save the attached file in some defined file system position
and let the scheduled task process the file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:20144
send them an sql file as they will not be able to login to msde remotely
"Randers" <rmonroe@.aulcorp.com> wrote in message
news:06cc01c52e3e$1e481e20$a501280a@.phx.gbl...
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
What is the best way to import data to a client's MSDE
database from our website? We want our client to log onto
our website so we can update a table on the client's PC. I
need a little direction on this, not sure which is the best
way to go.
Thank you,
Randers
hi Randers,
Randers wrote:
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
perhaps you could provide an ftp based solution to download new data from
your website and the MSDE remote client proceed with a scheduled job to bulk
insert it..
you can even set the job to notify via mail of the success/failure of it...
(just use an SMTP solution like http://www.sqldev.net/xp/xpsmtp.htm )
or mail them, but this become more manual as the final user has perhaps to
read the mail, save the attached file in some defined file system position
and let the scheduled task process the file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:20144
send them an sql file as they will not be able to login to msde remotely
"Randers" <rmonroe@.aulcorp.com> wrote in message
news:06cc01c52e3e$1e481e20$a501280a@.phx.gbl...
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
Wednesday, March 7, 2012
Importing data files into new instance of SQL Server
Hi Everyone,
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jon
if the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jon
if the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
Importing data files into new instance of SQL Server
Hi Everyone,
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jonif the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jonif the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
Importing data files into new instance of SQL Server
Hi Everyone,
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jonif the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
I've got an .MDF file (database) and an .LDF file (transaction log) that
were created using SQL Server 7.0. We've built a new windows server with
SQL Server 2000 installed and would like to import the files onto that
server. The problem is that we just have the files, the SQL Server 7.0
instance is gone. What is the simplest way to create a new database on the
SQL Server 2000 box with those files?
Thanks in Advance,
Jonif the files were previously detached from the db using sp_detach_db
you can just attach them using sp_attach_db. SQL Server should
do the upgrading for you. Of course, you'll need to drop and re-add
any users and roles.
"Jon Westmore" <westmoj@.reidhosp.com> wrote in message
news:%23c8rSoq7EHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi Everyone,
> I've got an .MDF file (database) and an .LDF file (transaction log) that
> were created using SQL Server 7.0. We've built a new windows server with
> SQL Server 2000 installed and would like to import the files onto that
> server. The problem is that we just have the files, the SQL Server 7.0
> instance is gone. What is the simplest way to create a new database on
the
> SQL Server 2000 box with those files?
> Thanks in Advance,
> Jon
>
Subscribe to:
Posts (Atom)