read xml file content - sql server 2005

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Hi

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

AS

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

GO

This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

--SQL Server 2005

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Create table #xmlcontent

(

Xml varchar(max)

)

Insert into #xmlcontent

Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')

Select XML From #XMLContent

Code Snippet

--SQL Server 2000

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Set @.Path = 'Type ' + @.Path

Create Table #XMLLines

(

Line Varchar(8000)

)

Insert Into #XMLLines

Exec xp_cmdshell @.Path

Select * from #XMLLines

|||

Hi

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function

Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

Hi

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

Hi

You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.

So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.

|||Many thanks|||

This sounds like a perfect job for SSIS!!

read xml file content

There is a folder which contains several different xml files.

Question

for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?

Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?

Thanks

Hi

Try this

Code Snippet

CREATE PROCEDURE [dbo].[spInsertXMLData]

(

@.strXML varchar(8000)

)

AS

DECLARE @.iDoc int

EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML

INSERT INTO Test (col1, col2, col3)

(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH

(col1 varchar(100),

col2 varchar(100),

col3 varchar(100)))

EXECUTE sp_XML_RemoveDocument @.iDoc

GO

This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.

Richard

|||

Hi,

yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.

Thanks

|||

Yes you can..

Code Snippet

--SQL Server 2005

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Create table #xmlcontent

(

Xml varchar(max)

)

Insert into #xmlcontent

Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')

Select XML From #XMLContent

Code Snippet

--SQL Server 2000

Declare @.Path as varchar(100);

Set @.Path = 'C:\Mani.Xml'

Set @.Path = 'Type ' + @.Path

Create Table #XMLLines

(

Line Varchar(8000)

)

Insert Into #XMLLines

Exec xp_cmdshell @.Path

Select * from #XMLLines

|||

Hi

This is how I have done it using ActiveX Scripting in a DTS package

Code Snippet

Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path

Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function

Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function

Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub

This will loop through a specified directory and read the contents of each file to a varchar and run the sp.

|||

hi,

Using sql 2005

error is :

Cannot bulk load. The file "C:\aud_df.xml" does not exist

The file is indeed in that location.

Thanks

|||

this seems to work on the server but not the local machine.?

Do you know why?

|||

Yes. It will read the data from client only.

Try with Server UNC path.. \\servername\folder\file.xml

|||

Hi

I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml

Richard

|||

What do you mean by UNC please?

Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.

Thanks

|||

Hi

You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.

So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.

|||Many thanks|||

This sounds like a perfect job for SSIS!!

Read the database transaction log file ie LDF file

Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
PushkarThere are various third-party tools that can help.
LogPI, Log Explorer, etc.
Recently I've been using Red Gate's tool quite a bit... I think it's a bit s
impler to use than the other available tools:
http://www.red-gate.com/sql/sql_log_rescue.htm
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:OOFQjIMqFHA.904@.TK
2MSFTNGP10.phx.gbl...
Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
Pushkar|||Use a third party tool.
http://www.aspfaq.com/2449
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:OOFQjIMqFHA.904@.TK2MSFTNGP10.phx.gbl...
Hi,
I want to read the .LDF for a database without making that database offline.
.LDF file contains all the transactions in that database.
Is there any documented/undocumented approach?
Any help is appreciated.
Thanks
Pushkar|||You can use the undocumented command DBCC LOG. See:
[url]http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp[/ur
l]
A better approach would be to use a third-party tool, such as
Lumigent Log Explorer, ApexSQL Log or Red-Gate SQL Log Rescue.
Razvan

read file and exec contents

I have a text file that contains "create index" statements - thanks to those
of you here who helped me get past my hurdles with this step earlier in the
w. :)
Now I need to read the file and execute it - basically execute the contents
of the file. I'm looking for suggestions on how to do it. I thought about
using the code below to read the contents into a global var but it seems as
if it only reads so many chars into a global var. My thought was to read it
into a global var then use an execute sql task to execute the global var.
Here is what I have so far. Any suggestion on the best way to apply the
create index statements in the text file?
Thanks, Andre
Function Main()
Set fso = CreateObject("Scripting.FileSystemObject")
Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
' msgbox textStreamObject.ReadAll
DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
Set textStreamObject = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Sample of my TableIndexes.txt file
ALTER TABLE [MYTABLE] ADD
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[RowID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GOWhy don't you just use OSQL or SQLCMD to do this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <no@.spam.com> wrote in message news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...darkred">
>I have a text file that contains "create index" statements - thanks to thos
e of you here who helped
>me get past my hurdles with this step earlier in the
> w. :)
> Now I need to read the file and execute it - basically execute the content
s of the file. I'm
> looking for suggestions on how to do it. I thought about
> using the code below to read the contents into a global var but it seems a
s if it only reads so
> many chars into a global var. My thought was to read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the c
reate index statements
> in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||Since you are reading the file from VBScript, you could simply store the
string in a local variable and execute as a statement using
ADODB.Command.Execute method call from the same script:
http://msdn.microsoft.com/library/d.../>
xecutex.asp
It seems there is no need to store the string in a DTS variable, just a
local variable. For example:
Dim strIndexes
If this task does not need to be schedules, then perhaps instead of DTS, use
Windows Scripting Host or OSQL from the command line.
"Andre" <no@.spam.com> wrote in message
news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>I have a text file that contains "create index" statements - thanks to
>those of you here who helped me get past my hurdles with this step earlier
>in the
> w. :)
> Now I need to read the file and execute it - basically execute the
> contents of the file. I'm looking for suggestions on how to do it. I
> thought about
> using the code below to read the contents into a global var but it seems
> as if it only reads so many chars into a global var. My thought was to
> read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the
> create index statements in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||It does need to be scheduled, and is actually part of a large DTS package.
I did change it to be in a local var rather than a global var - thanks for
that tip. It's working great now, thanks for your help.
Andre

Read Backup File Directly?

Is there a way to read a backup file to see if it contains a file without
restoring the backup file? We are missing a record from a table, and I need
to find out when it was deleted. We have nightly backup files of the
database. Is it possible, knowing the database name, the table name, and the
primary key value of the record, to determine if the record exists in a
certain backup file? If not, is there an alternate way to determine when a
particular record was deleted?

Thank you!

NeilNeil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

Is there a way to read a backup file to see if it contains a file
without restoring the backup file?

You can use RESTORE FILELISTONLY to list the backups within a backup file.

Quote:

Originally Posted by

We are missing a record from a table, and I need to find out when it was
deleted. We have nightly backup files of the database. Is it possible,
knowing the database name, the table name, and the primary key value of
the record, to determine if the record exists in a certain backup file?

You need to restore the backup. But you could restore it under a different
name.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Right. I was just that, if I wanted to find out *when* it was deleted, I'd
have to restore the backups one at a time, and look at the database after
each restore to see if it were there.

Thanks,

Neil

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955EDFCA1AF8Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>Is there a way to read a backup file to see if it contains a file
>without restoring the backup file?

>
You can use RESTORE FILELISTONLY to list the backups within a backup file.
>

Quote:

Originally Posted by

>We are missing a record from a table, and I need to find out when it was
>deleted. We have nightly backup files of the database. Is it possible,
>knowing the database name, the table name, and the primary key value of
>the record, to determine if the record exists in a certain backup file?

>
You need to restore the backup. But you could restore it under a different
name.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

rc:Section not working on Matrix report with SQL RS 2000

Hi
I've got a SQL RS 2000 report which contains a matrix that sometimes
contains over 100 rows which spans more than one vertical printed page
and I want the whole report to show on one page when viewed via the web
so that the user can see the whole report displayed in one view rather
than them having to page to different sections.
I was expecting the report to render all on one page with the
rc:Section specified as zero but the rc:Section parameter seems to be
ignored and only the first page of the report is shown. I've specified
rc:Section=0 in the URL access parameters and I have the report toolbar
showing as I want the user to be able to preview/print etc.
This only seems to be a problem with the matrix report. Has anyone come
across this, or does anyone know of a workaround?
JonThis is the full url device param list in case there's a collision
somewhere:
rs:Command=Render
rs:Format=HTML4.0
rs:ClearSession=False
rc:Toolbar=True
rc:Zoom=100
rc:HTMLFragment=true
rc:Section=0
rc:Parameters=False
rc:LinkTarget=_self
Jon

rc:linktarget does not work

I have a webform with 2 frames. The top frame contains the main report and clicking on one of the fields generates another report. I want this report to open in the bottom frame and not take over the whole window. I am using this in the 'jump to url' section of the text field:
="http:/ServerName/ReportServer?/My Reports/Test Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:LinkTarget=charts&rc:Parameters=False&StartMonth=1&EndMonth=8&reportYear=2004"
where 'charts' is the frame name.
It does not work. I am new to reporting services and have spent a lot of time trying to figure this out. Please help me. it's urget.
Thanks!!
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.You need the rc:LinkTarget parameter in the URL of the initial report. It
still needs to be in the Jump to URL, or else subsequent clicks break out of
the frame.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23EN34Xj0EHA.2824@.TK2MSFTNGP09.phx.gbl...
>I have a webform with 2 frames. The top frame contains the main report and
>clicking on one of the fields generates another report. I want this report
>to open in the bottom frame and not take over the whole window. I am using
>this in the 'jump to url' section of the text field:
> ="http:/ServerName/ReportServer?/My Reports/Test
> Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false&rc:LinkTarget=charts&rc:Parameters=False&StartMonth=1&EndMonth=8&reportYear=2004"
> where 'charts' is the frame name.
> It does not work. I am new to reporting services and have spent a lot of
> time trying to figure this out. Please help me. it's urget.
> Thanks!!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt