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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy