July 6, 2009 at 1:04 pm
I created the procedure
USE [xmltest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GET_MODELS] (@strXML xml)
AS
BEGIN
insert into MODEL
select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @strXML.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @strXML.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
END
And run it using - dbo.SP_GET_MODELS 'c:\data\test.xml'
But no rows are inserted. Am I placing the path correctly? Or is this syntex incorrect?
July 6, 2009 at 1:22 pm
you have to pass the contents of the xml instear of the xml file itself use something like:
SET @FileName = 'c:\data\test.xml'
SET @ExecCmd = 'type ' + @FileName
set @strXML = EXEC master.dbo.xp_cmdshell @ExecCmd
to pass the content of the file to the @strXML var.
July 6, 2009 at 1:53 pm
Ok. I tried that and added some declarations, but get a syntax error.
DECLARE @FileName nvarchar(50);
DECLARE @EXECCmd nvarchar(50);
DECLARE @strXML nvarchar(max);
SET @FileName = 'c:\data\test.xml'
SET @ExecCmd = 'type ' + @FileName
set @strXML = EXEC master.dbo.xp_cmdshell @ExecCmd
I get the error:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'EXEC'.
Once this works do I execute the procedure with -
dbo.SP_GET_MODELS (@strXML) - is this the correct syntax?
Sorry for my lack of knowledge here.
Thanks for you time!
July 6, 2009 at 3:30 pm
July 6, 2009 at 4:05 pm
Thanks for the help. I was able to pull the XML file contents into a variable, but I am unable to get it in a table. Here is the code I am trying. The @FileContents is varchar(8000) and I needed it to be type cast as xml for the XMLDT method nodes so I tried to set an xml variable @FileContents2 equal to @FileContents. Can you tell me what is wrong here?
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @FileContents2 XML
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'C:\data\test.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK
= @x
END
SELECT @FileContents as FileContents
SET @FileContents2 = @FileContents
BEGIN
insert into MODEL
select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @FileContents2.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @FileContents2.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
END
DROP TABLE #tempXML
July 6, 2009 at 4:14 pm
it works for me, just make sure the path to the file is based on the Server files and folders not your local client computer. Another thing is that the table MODEL i put as example must exist on the server. Change it for another temp table if you want add additional process to your data.
July 6, 2009 at 4:18 pm
The file is being read fine - When I do a "SELECT @FileContents as FileContents" I see the file contents. But no rows are being inserted into the model table.
July 6, 2009 at 4:26 pm
change the insert to this
INSERT INTO [SGA_DEMO].[dbo].[model]
([name]
,[val]
,[count]
,[rsid])
(select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @FileContents2.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @FileContents2.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
)
There must be something wrong with the parenthesis on the sub select query.
July 6, 2009 at 4:37 pm
Thanks, but it still does not work. What can I try to find the issue?
July 6, 2009 at 4:43 pm
this is my code,just check the paths and table name, also using existing temp table could cause the problem i change it to table variable:
DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
DECLARE @FileContents2 XML
declare @tempXML TABLE (PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'C:\data\test.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO @tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from @tempXML
SET @x = 0
WHILE @x @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from @tempXML WHERE PK = @x
END
SET @FileContents2 = @FileContents
BEGIN
INSERT INTO model([name],[val],[count],[rsid])
(select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @FileContents2.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @FileContents2.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
)
END
--testing contents
--truncate table model
select * from model
July 6, 2009 at 4:54 pm
Thanks - but your code did not fix the issue. Still no rows inserted in the table. What is your table definition? Mine is
USE [xmltest]
GO
/****** Object: Table [dbo].[MODEL] Script Date: 07/06/2009 17:53:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MODEL](
[name] [nvarchar](max) NULL,
[val] [nvarchar](max) NULL,
[count] [nvarchar](max) NULL,
[rsid] [nvarchar](max) NULL
) ON [PRIMARY]
GO
July 6, 2009 at 5:00 pm
Can you verify you are using this file?
July 6, 2009 at 5:18 pm
in this case the XML path to get the products has changed,
'/solution/results/financialresults/serverfamilytotal/modelstotal/product'
replace the insert as follow:
INSERT INTO model([name],[val],[count],[rsid])
(select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @FileContents2.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @FileContents2.nodes('/solution/results/financialresults/serverfamilytotal/modelstotal/product') AS Tbl(C)
)
July 6, 2009 at 5:21 pm
also make sure the file is on the server, and it is not already open.
you can declare all the varchars as (max) not only the fields in the table.
regards
July 6, 2009 at 6:46 pm
Thanks for spending so much time with me. It is still not working. I tried adding ServerFamilyTotal but it does not help. I do not think that was the issue, because ServerFamilyTotal is on the same level as Modelstotal.
When I execute the script I get the following messages:
(39 row(s) affected) - Reading the file
(0 row(s) affected) - nothing is being returned from the select on the file contents
(0 row(s) affected) - (select * from model)
(1 row(s) affected) - the (select @FileContents2 as FileContents)
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply