Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<COLUMNS>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

<Column name="Val2"> 2 </Column>

<Column name="Val3"> 3 </Column>

<Column name="Val4"> Test0 </Column>

<Column name="Val5"> Test1 </Column>

<Column name="Val6"> Test2 </Column>

<Column name="Val7"> Test3 </Column>

<Column name="Val8"> Test4 </Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@.DocHandle, '/XMLDATA/COLUMNS/Column',3)

WITH (Header varchar(50) '@.name',

Val varchar(50) 'text()')

EXEC sp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

sql

Reading a XML from table and passing it to sp_xml_preparedocument as i/p

Hi All,

I have a xml column in a table. As part of converting a XML into rowset i wrote a small proc like;

/****************************/

DECLARE @.DocHandle int

DECLARE @.XmlDocument nvarchar(1000)

SET @.XmlDocument = N'<XMLDATA>

<COLUMNS>

<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>

<Column name="Val2"> 2 </Column>

<Column name="Val3"> 3 </Column>

<Column name="Val4"> Test0 </Column>

<Column name="Val5"> Test1 </Column>

<Column name="Val6"> Test2 </Column>

<Column name="Val7"> Test3 </Column>

<Column name="Val8"> Test4 </Column>

</COLUMNS>

</XMLDATA>'

-- Create an internal representation of the XML document.

EXECsp_xml_preparedocument @.DocHandle OUTPUT, @.XmlDocument

-- Execute a SELECT statement using OPENXML rowset provider.

SELECT*

FROMOPENXML(@.DocHandle,'/XMLDATA/COLUMNS/Column',3)

WITH(Header varchar(50)'@.name',

Val varchar(50)'text()')

EXECsp_xml_removedocument @.DocHandle

/************************/

This works fine, but my query is how to modify this proc to read the XML from a table rather than hard code it in the proc itself.

Thanks in Advance

Since you mention xml column, so I assume you are using sql 2005. To process xml column in table especially you might have > 1 rows of xml data, you want to use xml nodes() method. For you example:

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)
go

Reading a long string

Hi there,
How can i see what a long string value is? I have a stored procedure which
creates a dynamic xml string and when i select it at the end of the SP the
result is about 100 chars long although i know it is about 7000 long and I
need to inspect the whole string, put it in an xml reader interface etc to
play around with. Even if i send the result to file, it is still truncated.
How can I read the whole string?
thanksWhat version of SQL are you using? XML data needs an XML client.
In SQL 2005 there's a built-in reader, that can display the XML in humanly
readable form, but for SQL 2000 you'd need a client application - look into
using SQLXML.
ML
http://milambda.blogspot.com/|||"louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> Hi there,
> How can i see what a long string value is? I have a stored procedure which
> creates a dynamic xml string and when i select it at the end of the SP the
> result is about 100 chars long although i know it is about 7000 long and I
> need to inspect the whole string, put it in an xml reader interface etc to
> play around with. Even if i send the result to file, it is still
> truncated.
> How can I read the whole string?
> thanks
If this is SQL 2k and you are using Query Analyser, ensure that you have
set your results length long enough.
Look at Tools->Options Choose Results tab, then put 8000 in the box for
Maximum Characters Per Column
Rick Sawtell
MCT, MCSD, MCDBA|||THANKS!!! i didnt realise you could set this option.
In response to ML - I am creating my own custom xml string. I wont go into
reasons but in a nutshell it is completely dynamic, not as simple as return
this resultset as xml. I then use that as an xml datasource in a .net page.
Regards
"Rick Sawtell" wrote:

> "louise raisbeck" <louiseraisbeck@.discussions.microsoft.com> wrote in
> message news:B8A6F36D-818D-44AB-A992-95744A9E1056@.microsoft.com...
> If this is SQL 2k and you are using Query Analyser, ensure that you have
> set your results length long enough.
> Look at Tools->Options Choose Results tab, then put 8000 in the box for
> Maximum Characters Per Column
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Read, write and update xml data type.

Hi All,

I would like to learn about xml data type of sql server 2005. I am using c# to develop a project that will use sql server express as a database. What I want to accomplish in my project is to serialize an object and save into a field with xml data type. Also I want to have same functionality in other way around. Retrieve this xml representation of the object, Deserialize it so get the saved object back into application.

I would be happy If you can provide me some code which shows how to accomplish this task or some links that directs me to the appropriate docs.

Thanks in advance.

There is lots of material on MSDN, there is a section about the xml data type with subsections about the methods of the xml data type (i.e. query, value, exist, modify, nodes) and the XML DML (XML data modification language).|||

Hi Martin,

Thanks for the reply and links. I haven't gone through the links you sent completly but I think the subject I typed here is misleading so let me explain a bit more, the difficulty I have. . I have no problem with creating a table with an xml type field. I believe by reading the links you have sent I can perform insert, update and delete functions. My difficulty starts just after the seriliazation of an object or just before the deserialization of the xml data I read from xml field. Both serialize and deserialize methods trys to write/read to/from a file or a stream. All I want is to hold this xml in a data structure where I can use it at the time of serialization or deserialization.

The original situaltion:

I have a form to be filled out by the users of my application. Since there are so many fields of data in this form I dont want to create a table with somany fields. So I am planing to hold info entered by user in an xml field. (and ofcourse I should be able to read the data back from this xml field and display in the app)

Thanks.

|||

Got it worked

I used string/TextWriter and string/TextReader combinations and worked fine. Thanks.

FlatWhite

|||

Hi FlatWhite

I would like to do the same thing you are doing. Could you provide some code snippets for me on how to do it?

Thanks

|||

Well I am neither SQL nor C# expert so you can use my code at your own risk J

First of all I am assuming that you have an SQL database table which has ID and XML fields and also you have InsertObj and SelectObj stored procedures.

TABLE : ObjectTable

ObjID-int-identity

ObjXML-XML

SPs : InsertObj

SelectObj

Code Snippet

CREATE PROCEDURE InsertObj

@.ObjXML xml

AS

BEGIN

INSERT INTO ObjectTable (ObjXML)

VALUES (@.ObjXML)

END

CREATE PROCEDURE SelectObj

@.ObjID int

AS

BEGIN

SELECT ObjXML

FROM ObjTable

WHERE ObjID = @.ObjID

END

I am also assuming that you have class Ojb with three properties property1, property2 and property3 and a form with 4 textboxes

Here is how you can serialize an object and save it in an XML field. (There might be some better way of doing this but sofar no one made a comment on this)

Code Snippet

Obj c = new Obj();

c.property1 = textBox1.Text;

c.property2 = textBox2.Text;

c.property3 = textBox3.Text;

XmlSerializer s = new XmlSerializer(typeof(Obj));

System.Text.StringBuilder builder = new System.Text.StringBuilder();

s.Serialize(XmlWriter.Create(builder),c);

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=Server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "InsertObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjXML", System.Data.SqlDbType.Xml);

command.Parameters[0].Value = builder.ToString();

command.ExecuteNonQuery();

conn.Close();

And this is how you can deserialize an XML field and get back the saved object.

Code Snippet

XmlReaderSettings set = new XmlReaderSettings();

set.ConformanceLevel = ConformanceLevel.Fragment;

Obj c = new Obj();

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "SelectObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjID", System.Data.SqlDbType.Int);

//taking the input from textBox4.

command.Parameters[0].Value = Convert.ToInt32(textBox4.Text);

SqlDataReader datareader = command.ExecuteReader();

System.Text.StringBuilder builder = new System.Text.StringBuilder();

XmlSerializer s = new XmlSerializer(typeof(Obj));

while(datareader.Read())

{

builder.Append(datareader[0]);

}

TextReader tr = new StringReader(builder.ToString());

c = (CObj)s.Deserialize(tr);

tr.Close();

textBox1.Text = c.property1;

textBox2.Text = c.property2;

textBox3.Text = c.property3;

I hope it helps.

Read, write and update xml data type.

Hi All,

I would like to learn about xml data type of sql server 2005. I am using c# to develop a project that will use sql server express as a database. What I want to accomplish in my project is to serialize an object and save into a field with xml data type. Also I want to have same functionality in other way around. Retrieve this xml representation of the object, Deserialize it so get the saved object back into application.

I would be happy If you can provide me some code which shows how to accomplish this task or some links that directs me to the appropriate docs.

Thanks in advance.

There is lots of material on MSDN, there is a section about the xml data type with subsections about the methods of the xml data type (i.e. query, value, exist, modify, nodes) and the XML DML (XML data modification language).|||

Hi Martin,

Thanks for the reply and links. I haven't gone through the links you sent completly but I think the subject I typed here is misleading so let me explain a bit more, the difficulty I have. . I have no problem with creating a table with an xml type field. I believe by reading the links you have sent I can perform insert, update and delete functions. My difficulty starts just after the seriliazation of an object or just before the deserialization of the xml data I read from xml field. Both serialize and deserialize methods trys to write/read to/from a file or a stream. All I want is to hold this xml in a data structure where I can use it at the time of serialization or deserialization.

The original situaltion:

I have a form to be filled out by the users of my application. Since there are so many fields of data in this form I dont want to create a table with somany fields. So I am planing to hold info entered by user in an xml field. (and ofcourse I should be able to read the data back from this xml field and display in the app)

Thanks.

|||

Got it worked

I used string/TextWriter and string/TextReader combinations and worked fine. Thanks.

FlatWhite

|||

Hi FlatWhite

I would like to do the same thing you are doing. Could you provide some code snippets for me on how to do it?

Thanks

|||

Well I am neither SQL nor C# expert so you can use my code at your own risk J

First of all I am assuming that you have an SQL database table which has ID and XML fields and also you have InsertObj and SelectObj stored procedures.

TABLE : ObjectTable

ObjID-int-identity

ObjXML-XML

SPs : InsertObj

SelectObj

Code Snippet

CREATE PROCEDURE InsertObj

@.ObjXML xml

AS

BEGIN

INSERT INTO ObjectTable (ObjXML)

VALUES (@.ObjXML)

END

CREATE PROCEDURE SelectObj

@.ObjID int

AS

BEGIN

SELECT ObjXML

FROM ObjTable

WHERE ObjID = @.ObjID

END

I am also assuming that you have class Ojb with three properties property1, property2 and property3 and a form with 4 textboxes

Here is how you can serialize an object and save it in an XML field. (There might be some better way of doing this but sofar no one made a comment on this)

Code Snippet

Obj c = new Obj();

c.property1 = textBox1.Text;

c.property2 = textBox2.Text;

c.property3 = textBox3.Text;

XmlSerializer s = new XmlSerializer(typeof(Obj));

System.Text.StringBuilder builder = new System.Text.StringBuilder();

s.Serialize(XmlWriter.Create(builder),c);

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=Server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "InsertObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjXML", System.Data.SqlDbType.Xml);

command.Parameters[0].Value = builder.ToString();

command.ExecuteNonQuery();

conn.Close();

And this is how you can deserialize an XML field and get back the saved object.

Code Snippet

XmlReaderSettings set = new XmlReaderSettings();

set.ConformanceLevel = ConformanceLevel.Fragment;

Obj c = new Obj();

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "SelectObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjID", System.Data.SqlDbType.Int);

//taking the input from textBox4.

command.Parameters[0].Value = Convert.ToInt32(textBox4.Text);

SqlDataReader datareader = command.ExecuteReader();

System.Text.StringBuilder builder = new System.Text.StringBuilder();

XmlSerializer s = new XmlSerializer(typeof(Obj));

while(datareader.Read())

{

builder.Append(datareader[0]);

}

TextReader tr = new StringReader(builder.ToString());

c = (CObj)s.Deserialize(tr);

tr.Close();

textBox1.Text = c.property1;

textBox2.Text = c.property2;

textBox3.Text = c.property3;

I hope it helps.

Read xml from sql server column(xml data type) and return as XmlDocument in C#

Hi Everyone:

I would appreciate it if someone can help me this problem as I am a c# beginner. I am writing a C# method that is supposed to read in XML from SQL Server 2005 Database. The table I am reading from has a column which is set to XML data type and has a well formed XML document. I would like to load that xml into a xml document in my c# and return back as a xmldocument in my method. Here is my method so far, as you can see i am utilizing the Enterprise Library Data Access Block for db access. I woud appreciate if you can provide me with some help. Thanks.

Note: What i am doing right now maybe completely off track from what I am trying to achieve. here is the code I have so far:

private const string SQLGETCNFS = "SELECT EntityDefinitionXML FROM EntityDefinition WHERE EntityID = ";

/// <summary>
/// Method retrieves XML Entity Definition from the configuration database
/// </summary>
/// <param name="entityID"></param>
/// <returns>XmlDocument Type</returns>
public XmlDocument GetCustomerCNSFData(string entityID)
{
//connect to config database
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = SQLGETCNFS + entityID.ToString();

IDataReader reader = db.ExecuteReader(CommandType.Text, sqlCommand);

while (reader.Read())
{
SqlXml sx = reader.GetSqlXml(1);
XmlReader xr = sx.CreateReader();
xr.Read();
}
return null;
}

You can create a new instance of XmlDocument and then call the Load method and pass the XmlReader that you created from SqlXml.

Regards,

Galex Yen

Read xml file, store data in db. What could be easier?

OK guys,
I am going to receive an xml doc and am going to have to parse the data
values and store them in the database. About 10 db tables are targets for the
data, with repeating groups of data and such. I have not done this with Sql
Server before. I need to be pointed in the right direction. So start
pointing!
Thanks,
Michael
These xpath query samples are suitable for COM and
for .NET.
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:58E7DD5B-CEAF-4677-902A-0A7BBBF0DA30@.microsoft.com...
> OK guys,
> I am going to receive an xml doc and am going to have to parse the data
> values and store them in the database. About 10 db tables are targets for
> the
> data, with repeating groups of data and such. I have not done this with
> Sql
> Server before. I need to be pointed in the right direction. So start
> pointing!
> Thanks,
> Michael
sql

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 XML > 8k in sql TEXT column?

Hi,
I have a SQL2k database that holds XML in a TEXT column because it is
greater than 8k, I need to extract the value from a couple of fields in the
XML. How can I do this? Examples would be great; I'm new to world of XML.
BTW I'm don't have control of the database design so I can't change the
structure of the db to hold the data in a more senisble way.
Thanks,
Paul.Hello Paul,
Thank you for posting.
Regarding on the read XML data from multiple columns in SQLServer database,
are you using ADO.NET components to access the database table? Based on my
understanding, if we can make sure the content order of those columns in
the database table, we can just use ADO.NET datareader or dataadapter to
query the records out, and then combine the text in those columns together
to construct a complete text stream.
Please let me know if you have any detailed question or concerns here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Steven,
Thanks for the reply, I'm not using ADO.NET.
All the data is in a single SQL2k Text column but it is greater than 8k in
size. What I'm looking to do get data out of a couple of elements and write
them back to two sql columns using a stored procedure.
The basic issue is because I can't delclare a variable of type text in the
procedure. How can I use the sp_xml_preparedocument and OPENXML commands in
a
procedure with a large text column with more than 8k of XML.
Thanks,
Paul.
"Steven Cheng[MSFT]" wrote:

> Hello Paul,
> Thank you for posting.
> Regarding on the read XML data from multiple columns in SQLServer database
,
> are you using ADO.NET components to access the database table? Based on my
> understanding, if we can make sure the content order of those columns in
> the database table, we can just use ADO.NET datareader or dataadapter to
> query the records out, and then combine the text in those columns together
> to construct a complete text stream.
> Please let me know if you have any detailed question or concerns here.
> Regards,
>
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Thanks for your response Paul,
So you're going to done the multiple text column string concatenate at
server-side through T-SQL. Based on my research, I'm afraid this is not
supported in SQL 2000 since the datatype are limited to varchar or ntext
which has 8000 limitation. And we can not have local variable that have
larger value return from concatenate of such columns. So we may consider
done it at upstream level(in data access component, ADO or ADO.NET).
BTW, if it is possible to upgrate to SQL 2005, there has built-in sql xml
type and CLR code supported which may help resolve such issue.
Sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Read Value from XML

This is My example :
DECLARE @.idoc int
DECLARE @.doc varchar(4000)
SET @.doc ='
<NEWDATASET>
<TABLE1>
<INTERO>1</INTERO>
</TABLE1>
</NEWDATASET> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
WITH (INTERO int)
Not function, why?
John,
You need to identify the element in the WITH clause... also, don't forget to
remove the document reference when you're done:
DECLARE @.idoc int
DECLARE @.doc varchar(4000)
SET @.doc ='
<NEWDATASET>
<TABLE1>
<INTERO>1</INTERO>
</TABLE1>
</NEWDATASET> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
WITH (INTERO int 'INTERO')
exec sp_xml_removedocument @.idoc
"John" <ch@.msn.com> wrote in message
news:%23id3kIfsEHA.2128@.TK2MSFTNGP11.phx.gbl...
> This is My example :
> DECLARE @.idoc int
> DECLARE @.doc varchar(4000)
> SET @.doc ='
> <NEWDATASET>
> <TABLE1>
> <INTERO>1</INTERO>
> </TABLE1>
> </NEWDATASET> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
> WITH (INTERO int)
>
> Not function, why?
>

Read the filename, split it and put it in a table

Hello

I'm working on a package which loops through each xml file in a folder.
The name of each xml file is put in variable.
The format of the filename is something like "part1_part2_part3.xml"
I need to store the 3 parts in three different columns of table A
The content of the xml file needs to be manipulated ("." needs to be replaced with ",", ....)and put in serveral columns in tableB

It's not clear to me yet how to start this but my main concern is read the three parts of the filename. I don't find any task in SSIS which could help me with that.

Could someone give me some pointers?

Many thanks!

Worf

Since you alreday have the name of the file in a varibale; then create 3 extra variables and use the expression property for getting the part name. when looking into a variable press F4 to display the property panel and then change the property 'EvaluateAsExpression' to true; then you would have access to the expression builder. There are some string functions there.

Rafael Salas

|||how do you get the filename into a variable?|||

Cobr94 wrote:

how do you get the filename into a variable?

This a previous tread where i described something similar; I hope you can use it

Rafael Salas

sql

Read of XML File

Hello,
I do have XML file in c:\project.xml, this file is arround 60 MB file.
Now i want to use
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc and then OPENXML to load xml
into sql server table, so how can i pass that xml file to
EXEC sp_xml_preparedocument
Pls helpTry this:
http://www.sqlxml.org/faqs.aspx?faq=39
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:B51412BA-DDD2-443C-97B4-4E4E76358A2E@.microsoft.com...
> Hello,
> I do have XML file in c:\project.xml, this file is arround 60 MB file.
> Now i want to use
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc and then OPENXML to load
xml
> into sql server table, so how can i pass that xml file to
> EXEC sp_xml_preparedocument
> Pls help|||Is there anyway so that i c an do bulk load..because my .xml file is very
big, arround 60MB. Pls let me know
"Narayana Vyas Kondreddi" wrote:

> Try this:
> http://www.sqlxml.org/faqs.aspx?faq=39
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:B51412BA-DDD2-443C-97B4-4E4E76358A2E@.microsoft.com...
> xml
>
>

Read all xml files from a folder

I have a script that can read specific files that are in an xml format, but i
need to modifiy it to read all files from a folder. In this case, I do not
know the file names but I do know that I need to read all of them. Does
anyone have a VB activeX script for this for MS SQL server 2000?
Thanks
You would use the Scripting.FileSystemObject to iterate
through the files in folder:
http://www.eggheadcafe.com/articles/20030627b.asp
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>I have a script that can read specific files that are in an xml format, but
>i
> need to modifiy it to read all files from a folder. In this case, I do
> not
> know the file names but I do know that I need to read all of them. Does
> anyone have a VB activeX script for this for MS SQL server 2000?
> Thanks
|||Thanks Robbe. Your post/page/article worked like a charm!!
Ben
"Robbe Morris [C# MVP]" wrote:

> You would use the Scripting.FileSystemObject to iterate
> through the files in folder:
> http://www.eggheadcafe.com/articles/20030627b.asp
> --
> 2004 and 2005 Microsoft MVP C#
> Robbe Morris
> http://www.masterado.net
> Earn $$$ money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>
>
sql

Read a remote web file and parse it

As subject, I need to read a remote web file, csv or xml isn't important,
from a t-sql sp scheduled in a job.
I don't know how to write the remote read steps. Any helps?
AndreaHi
Can you FTP the file? In which case you could use the DTS "File Transfer
Protocol" task to do this although if you used FTP.EXE from the "Execute
Process" or a xp_cmdshell call in a "Execute SQL" task" you may have more
control.
John
Andrea Moro" <moroandrea@.tiscali.it> wrote in message
news:4274c427$0$20669$5fc30a8@.news.tiscali.it...
> As subject, I need to read a remote web file, csv or xml isn't important,
> from a t-sql sp scheduled in a job.
> I don't know how to write the remote read steps. Any helps?
> Andrea
>

RDL File XML Comments lost

Hi
Using Visual Studio 2003...
So I've got a report with just under 21000 lines of XML in the RDL file.
Then I make a typo in an expression and get a non-specified build error. To
work out where the error was I commented out the xml using the <![CDATA[
**XML ROWS HERE** ]]>
I then did a rebuild to see if I had "removed" the error and I had. Good, so
I know what section of the report to look at more closely.
I go back to the RDL file and WHAT!!! my comments and the contained code has
been deleted.
Does anyone know if this is correct? Can I get the code back (undo (ctrl+z)
doesn't work).
How do other people comment out the RDL code'
SimonVery few people touch the RDL code directly. I have for a few specify things
(usually to get around issues with unnamed parameters losing their mapping
so I would modify the query in the RDL code instead of going into the UI to
do this). About the only people I know who would be doing this are the few
vendors creating their own tools that generate RDL.
Is there a reason you are putting an expression into the RDL rather than
using the Report Designer?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"simonb" <simonb@.discussions.microsoft.com> wrote in message
news:A44387D0-A1BA-4BB9-9B79-6EA95E9C1D2E@.microsoft.com...
> Hi
> Using Visual Studio 2003...
> So I've got a report with just under 21000 lines of XML in the RDL file.
> Then I make a typo in an expression and get a non-specified build error.
> To
> work out where the error was I commented out the xml using the <![CDATA[
> **XML ROWS HERE** ]]>
> I then did a rebuild to see if I had "removed" the error and I had. Good,
> so
> I know what section of the report to look at more closely.
> I go back to the RDL file and WHAT!!! my comments and the contained code
> has
> been deleted.
> Does anyone know if this is correct? Can I get the code back (undo
> (ctrl+z)
> doesn't work).
> How do other people comment out the RDL code'
> Simon
>|||Simon,
I would check in a copy into source control. This will allow you to go back
to it no matter what the report designer does.
FYI, I've seen elements and attributes reorded by the designer, so I assumed
it was parsing the RDL and then regenerating it from scratch. It doesn't
surprise me at all that you would lose comments and formatting.
Ted
"simonb" wrote:
> Hi
> Using Visual Studio 2003...
> So I've got a report with just under 21000 lines of XML in the RDL file.
> Then I make a typo in an expression and get a non-specified build error. To
> work out where the error was I commented out the xml using the <![CDATA[
> **XML ROWS HERE** ]]>
> I then did a rebuild to see if I had "removed" the error and I had. Good, so
> I know what section of the report to look at more closely.
> I go back to the RDL file and WHAT!!! my comments and the contained code has
> been deleted.
> Does anyone know if this is correct? Can I get the code back (undo (ctrl+z)
> doesn't work).
> How do other people comment out the RDL code'
> Simon
>|||Hi Bruce
I find that some times when you make an error in the designer (i.e. put an
extra closing braket on an "IIF" statement in an expression), and then
rebuild the report you are told that there was an error but there are no
details of where to look.
If you've been working on a few things since the last build you don't want
to undo all your changes, so in the past I have opened the RDL file CUT the
xml code containing the table/rectangle or whatever I think the error might
be in and PAST this code into notepad to store it. Then I rebuild the report
and see if the error is still there. Just basic trouble shooting stuff.
This time I thought I'd just comment it out in the RDL file instead :(
Simon
"Bruce L-C [MVP]" wrote:
> Very few people touch the RDL code directly. I have for a few specify things
> (usually to get around issues with unnamed parameters losing their mapping
> so I would modify the query in the RDL code instead of going into the UI to
> do this). About the only people I know who would be doing this are the few
> vendors creating their own tools that generate RDL.
> Is there a reason you are putting an expression into the RDL rather than
> using the Report Designer?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "simonb" <simonb@.discussions.microsoft.com> wrote in message
> news:A44387D0-A1BA-4BB9-9B79-6EA95E9C1D2E@.microsoft.com...
> > Hi
> >
> > Using Visual Studio 2003...
> >
> > So I've got a report with just under 21000 lines of XML in the RDL file.
> > Then I make a typo in an expression and get a non-specified build error.
> > To
> > work out where the error was I commented out the xml using the <![CDATA[
> > **XML ROWS HERE** ]]>
> >
> > I then did a rebuild to see if I had "removed" the error and I had. Good,
> > so
> > I know what section of the report to look at more closely.
> >
> > I go back to the RDL file and WHAT!!! my comments and the contained code
> > has
> > been deleted.
> >
> > Does anyone know if this is correct? Can I get the code back (undo
> > (ctrl+z)
> > doesn't work).
> >
> > How do other people comment out the RDL code'
> >
> > Simon
> >
>
>|||That makes sense. I thought you were perhaps trying to build the RDL from
scratch. I go into the RDL to work around things from time to time too.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"simonb" <simonb@.discussions.microsoft.com> wrote in message
news:CC54B564-F2BB-4AD0-822D-0192558510B3@.microsoft.com...
> Hi Bruce
> I find that some times when you make an error in the designer (i.e. put an
> extra closing braket on an "IIF" statement in an expression), and then
> rebuild the report you are told that there was an error but there are no
> details of where to look.
> If you've been working on a few things since the last build you don't want
> to undo all your changes, so in the past I have opened the RDL file CUT
> the
> xml code containing the table/rectangle or whatever I think the error
> might
> be in and PAST this code into notepad to store it. Then I rebuild the
> report
> and see if the error is still there. Just basic trouble shooting stuff.
> This time I thought I'd just comment it out in the RDL file instead :(
>
> Simon
> "Bruce L-C [MVP]" wrote:
>> Very few people touch the RDL code directly. I have for a few specify
>> things
>> (usually to get around issues with unnamed parameters losing their
>> mapping
>> so I would modify the query in the RDL code instead of going into the UI
>> to
>> do this). About the only people I know who would be doing this are the
>> few
>> vendors creating their own tools that generate RDL.
>> Is there a reason you are putting an expression into the RDL rather than
>> using the Report Designer?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "simonb" <simonb@.discussions.microsoft.com> wrote in message
>> news:A44387D0-A1BA-4BB9-9B79-6EA95E9C1D2E@.microsoft.com...
>> > Hi
>> >
>> > Using Visual Studio 2003...
>> >
>> > So I've got a report with just under 21000 lines of XML in the RDL
>> > file.
>> > Then I make a typo in an expression and get a non-specified build
>> > error.
>> > To
>> > work out where the error was I commented out the xml using the
>> > <![CDATA[
>> > **XML ROWS HERE** ]]>
>> >
>> > I then did a rebuild to see if I had "removed" the error and I had.
>> > Good,
>> > so
>> > I know what section of the report to look at more closely.
>> >
>> > I go back to the RDL file and WHAT!!! my comments and the contained
>> > code
>> > has
>> > been deleted.
>> >
>> > Does anyone know if this is correct? Can I get the code back (undo
>> > (ctrl+z)
>> > doesn't work).
>> >
>> > How do other people comment out the RDL code'
>> >
>> > Simon
>> >
>>

RDL and XML

Reporting Services report definition language or rdl looks like XML.

What are the differecnes?

Can RDL be used with Office 2003 i.e Word or Excel?

Regards

J

RDL is XML.

XML is a standard and RDL files are specific XML files for Reporting Services.

Word 2003 can read XML file in this own format so Word 2003 does not recongnize .RDL files.

Please open the RDL file with NOTEPAD.EXE and open one Word 2003 XML File with NOTEPAD.EXE.

RDF XML

I know SQL Server 2005 can import/export xml files. Can it be specific for RDF XML files?

There is nothing specific in SQL Server for RDF-XML processing.