Mining XML from file

Right now, when i want to load contents of XLM file, i have to use openXML which looks this way:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc
select *
from openxml (@.idoc, '/root/tag',3 )
( and here is whole select procedure)
insert into (table name)
from openxml (@.idoc, '/root/tag,3 )
(and here it is again)

This is quite "useless" for daily using, unless someone fills the text field with new data.

Is it possible to load content directly from XML file located somewhere on the drive?Is there any other way to open XML file an load its contents into table?|||

What "text field" are you talking about?

Have you looked at the SQL Server Integration Services?

|||Whole content is loaded as text. It has "something" to do with this part:

declare @.idoc int
declare @.doc varchar(max)
set @.doc='<?xml version="1.0" encoding="windows-1250"?>
<root>
<tag>
<content1>xxccxcx</content1>
.
.
.
<contentXY>sf</contentXY>
</tag>
</root>
exec sp_xml_preparedocument @.idoc output, @.doc

If i wanted an visual studio solution, which i already have, i would put it in visual studio forum.

But still integration package is another file, artificially created. I dont need those.|||You could use bulk load:
1. All data to one field
insert into your_table
select * from openrowset( BULK 'c:\yourfile.txt' SINGLE_BLOB)
2. Split data to few rows
insert into your_table

select * from openrowset( BULK 'c:\yourfile.txt' FORMATFILE='C:\bcpformat.xml')

More info: http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Also you could load data into xml column and use xml.nodes function insted of openxml for split xml-data to rows/filed|||

I have the same issue as errpop although i am running SQL 2005 std 1 CPU SP1 on Win 2000 server SP4 (both fully patched clean installs). The errors i am getting in the sql server logs are below and occur immediately after the sql server service starts and the number of cpu's are detected and written to the sql logs.

Error: 8313, Severity: 16, State: 1.

Error in mapping SQL Server performance object/counter indexes to object/counter names. SQL Server performance counters are disabled.

Error: 3409, Severity: 16, State: 1.

Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.

I have followed the instructions in KB300956 and nothing changed. I noticed executing lodctr sqlctr.ini runs but i dont get any messages on screen. It is like it is not working. The reason i am so interested in the performance counters is i am trying to troubleshoot a local HP SureStore DLT1 tape drive (14 hours to backup 70GB) and i need the SQL backup device counter. I dont know if this is related or not?

Any help is greatly appreciated as this is entirely new territory to me.