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 Text File from SQL Server, Read its content, and load it in RichTextBox (Related comp

OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBox

THINGS I'VE DONE AND HAVE WORKING:
1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image)

2) I've also able to load the file using a Handler as below:

using System;
using System.Web;
using System.Data.SqlClient;

public class HandlerImage : IHttpHandler {

string connectionString;
public void ProcessRequest (HttpContext context)
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["NWS_ScheduleSQL2000"].ConnectionString;
int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);
SqlConnection myConnection = new SqlConnection(connectionString);

string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@.Image_Id";
SqlCommand cmd = new SqlCommand(Command, myConnection);
cmd.Parameters.Add("@.Image_Id", System.Data.SqlDbType.Int).Value = ImageID;

SqlDataReader dr;

myConnection.Open();
cmd.Prepare();
dr = cmd.ExecuteReader();
if (dr.Read())
{ //WRITE IMAGE TO THE BROWSER
context.Response.ContentType = dr["Image_Type"].ToString();
context.Response.BinaryWrite((byte[])dr["Image"]);
}
myConnection.Close();
}

public bool IsReusable {
get {
return false;
}
}

}'>'>

<ahref='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>

File

</a>
- Click on this link, I'll be able to download or view the file

WHAT I WANT TO DO, BUT HAVE PROBLEM:
- I would like to be able to read CONTENT of this file and load it in a string as below

StreamReader SR = new StreamReader()
SR = File.Open("File.txt");
String contentText = SR.Readline();
txtBox.text = contentText;

BUT THIS ONLY WORK FOR files in the server.
I would like to be able to read FILE CONTENTS from SQL Server.

PLEASE HELP. I really appreciate it.

I've figured it out, but it takes too long for my browser to load, and end up with string that include many squares like "Submitted 22155 1966 Servers".

Below is my code that goes with what is above.

Stream theFile =newMemoryStream((byte[])dr["Doc"]);

StreamReader SR =newStreamReader(theFile);

String contentText = SR.ReadToEnd();

If anybody can tell me why it's so slower to load the content of the text file on browser compare to uploading it to the database, and a way to speed it up

I'll really appreciate it.

Thanks,

Read Image Data From SQL Server

Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.

Thanks Jerry

oSQLConn.Open()

Dim myreaderAs SqlDataReader

myreader = myCommand.ExecuteReader

Response.Expires = 0

Response.Buffer =True

Response.Clear()

DoWhile (myreader.Read())

Response.ContentType = ("application/pdf")

Response.BinaryWrite(myreader.Item("img_content"))

Loop

Hi,

From your description, it seems that you are going to retrieve the PDF data in tabbed section of the current aspx file, right?

Well, in your scenario, I noticed that you are going to reset the content type to "application/pdf", but the aspx page's content type is "text/html", they cannot be displayed at the same page.

I guess you have comment the "Response.contenttype.." line, right? And you are using Resposne. Binary Write to write the specified information to the current HTTP output. I suggest that you can have a try to wrap all these codes into a user control, use this control in your tabbed section of your current aspx page and try to see if it can work.

Thanks.

|||

Thanks for your response. Maybe this description will help more. I havea tab control on an aspx form. Each tab needs to display the picture, pdf etc etc associated with that particular tab for a specific user. The binary data is stored in a sql server 2005 image field. What would be the best way to achieve the desired result. What would be a good way to handle this? I should also add there may be more then one pdf/image for a particular section.

Thanks

Jerry

|||

Hi,

Well, you can add a Image control in your Tab control container, and create a page which works for displaying the image data from database.

The code snippet on that page to show the image data: (ShowImg.aspx)

// myRead is a DataReader object.
Byte[] Buffer = (Byte[])myRead[0];

//Output
this.Response.Clear();
this.Response.BinaryWrite(Buffer);
this.Response.End();

And then, assign the ImageUrl property of the Image control.

this.Image1.ImageUrl = "showimg.aspx"

Of course, if you want to show different pictures based on the parameters like following:

this.Image1.ImageUrl = "showimg.aspx?imageid=123"

Then, you can receive the parameter on ShowImg.aspx, make a query with that parameter against the database and return the corresponding image data.

Thanks.

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?
Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:

> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
>
>
|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
[vbcol=seagreen]
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?Hi
I assume you are using the Open table option in SSMS, in which case if your
column has data it will say <binary data> if you have data in a image or
varbinary column. If you ran select query in a query windows against the
column you would see the data itself. Blob data is stored in the database,
with SQL 2008 and the filestream option for varbinary(max) you can store
your blobs on the filesystem.
John
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> How to read the content of a BLOB field under SQL 2005 Management Studio?
> under the Management Studio, how to tell the blob field contain
> information
> or not.
> Does the BLOB content store in the .mdb file or somewhere else?
>
>|||Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:
> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > How to read the content of a BLOB field under SQL 2005 Management Studio?
> >
> > under the Management Studio, how to tell the blob field contain
> > information
> > or not.
> >
> > Does the BLOB content store in the .mdb file or somewhere else?
> >
> >
> >
>
>|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the Open table option in SSMS, in which case if your
> > column has data it will say <binary data> if you have data in a image or
> > varbinary column. If you ran select query in a query windows against the
> > column you would see the data itself. Blob data is stored in the database,
> > with SQL 2008 and the filestream option for varbinary(max) you can store
> > your blobs on the filesystem.
> >
> > John
> >
> > "Kam" <Kam@.discussions.microsoft.com> wrote in message
> > news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > > How to read the content of a BLOB field under SQL 2005 Management Studio?
> > >
> > > under the Management Studio, how to tell the blob field contain
> > > information
> > > or not.
> > >
> > > Does the BLOB content store in the .mdb file or somewhere else?
> > >
> > >
> > >
> >
> >
> >