January 27, 2010 at 3:32 pm
Hello,
I have hundreds of XML files and want to shred them into SQL server 2005.
I m using a simple script as bellow.
I m trying to pass the file name as parameter but I have no success so fare. any help will be much appreciate. thanks
DECLARE @doc xml;
BEGIN
Set @myFileNamevar =N'C:\bidon\sales.xml'
SET @doc = (
SELECT * FROM OPENROWSET (
BULK 'C:\bidon\sales.xml',SINGLE_BLOB
) AS xmldata
)
insert into dbo.xmlToData (ProductCode,Quantity, UnitPrice)
SELECT Node.value('@ProductCode', 'int') as ProductCode,
Node.value('@Quantity', 'int') as Quantity,
Node.value('@UnitPrice', 'int') as UnitPrice
FROM @doc.nodes('/SalesInvoice/Items/Item') TempXML (Node);
END
February 2, 2010 at 6:38 pm
thanks, here is my final version, it's working perfectly !
DECLARE @wholeFileName varchar(200)
DECLARE @PathFileName varchar(200)
DECLARE @FName varchar(30)
DECLARE @RowCnt int
DECLARE @MaxRows int
SET @PathFileName ='\\my_path\'
DECLARE @tabfileNames TABLE ( rownum int IDENTITY (1, 1) Primary key NOT NULL, FName varchar(100) )
INSERT INTO @tabfileNames
exec master.dbo.xp_cmdshell 'dir \\my_path\*.xml/b'
SELECT @MaxRows=count(*) from @tabfileNames
SELECT @RowCnt = 1
WHILE @RowCnt <= @MaxRows
BEGIN
SELECT @FName = FName FROM @tabfileNames WHERE rownum = @RowCnt
SET @wholeFileName=@PathFileName + @FName;
exec('DECLARE @doc xml SET @doc = (
SELECT convert(xml, BulkColumn, 2)FROM OPENROWSET (
BULK ''' + @wholeFileName +''' ,SINGLE_BLOB
) AS xmldata )
insert into my_table
select * from (
select
TempXML.Node.value(''@doc-number'', ''varchar(200)'') as doc_number,
TempXML2.Node2.value(''(/name)[1]'', ''varchar(200)'') as name,
FROM @doc.nodes(''/my_element'') TempXML (Node)
CROSS APPLY TempXML.Node.nodes(''//my_other_element'') TempXML2 (Node2)
)a
' ) ;
SET @RowCnt = @RowCnt + 1
END /*while*/
END
February 2, 2010 at 6:52 pm
please don't cross post. Answers and posts should be posted to
http://www.sqlservercentral.com/Forums/Topic855268-338-1.aspx#bm858349
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply