April 8, 2009 at 3:25 am
Dear all,
I have a test.dat file in attachment.
I am finding how to import the "time,code,target.." in to SQL database.
any one know how to do.
Please help
thank you.
April 8, 2009 at 3:29 am
The easiest way would be to use the import/export wizard in SSMS
April 8, 2009 at 6:28 am
DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET
(
BULK 'D:\Test1.dat', SINGLE_BLOB
) X
SELECT @xml
SELECT X.C.value('text()[1]', 'nvarchar(50)') as Message,
X.C.value('(/Msg/@time)[1]', 'datetime') as [Time],
X.C.value('(/Msg/@type)[1]', 'nvarchar(50)') as [Type]
FROM @xml.nodes('/Msg') X(C)
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
April 8, 2009 at 8:03 pm
Every run well.
Thank you very much.
April 8, 2009 at 10:25 pm
Hello Artur Sokhikyan,
Thank you again for you very well code, I still have a problem.
that is :the code you gave me can only show the data for the first row.
it looks like we point the array index to be only 1
sample code: X.C.value('text()[1]','nvarchar(50)') as Message,
So, do you have any how to show all existed records?
Thank you very much.
April 9, 2009 at 5:54 am
try this.
DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET
(
BULK 'D:\Test1.dat', SINGLE_BLOB
) X
SELECT
C.value('.', 'nvarchar(50)') as [Message],
C.value('@time', 'datetime') as [Time],
C.value('@type', 'nvarchar(50)') as [Type],
C.value('@code', 'nvarchar(50)') as [Code]
FROM @xml.nodes('/Msg') X(C)
-------------------------
- Name?
- Abu Dalah Sarafi.
- Sex?
- 3 times a week!
- No, no. Male or Female?
- Male, female, sometimes camel...
April 9, 2009 at 7:16 pm
Yes,this is the perfect code.
Thank you very much
Yoothasak,
June 15, 2016 at 4:33 am
The query picked up few records. How to make it read whole file
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply