SP to insert XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP to insert XML

Author

Topic

redcamel
Starting Member

1 Post

Posted - 2009-04-27 : 20:07:03

I’m trying to create the following stored proc…I’m loosing my mind. Can someone help me out…I get the following error

Msg 156, Level 15, State 1, Procedure sp_foo, Line 5
Incorrect syntax near the keyword 'INSERT'.

Code:

Create Procedure sp_foo
(
@xml xml
)
INSERT INTO dbo.VMWARE (Entity, [Type], [Time], [Metric], [AVG], [MAX], [MIN])
SELECT
doc.col.value('Entity[1]', 'varchar(255)') Entity,
doc.col.value('Type[1]', 'varchar(255)') [Type],
doc.col.value('Time[1]', 'datetime') [Time],
doc.col.value('Metric[1]', 'varchar(255)') [Metric],
doc.col.value('AVG[1]', 'int') [AVG],
doc.col.value('MAX[1]', 'int') [MAX],
doc.col.value('MIN[1]', 'int') [MIN]
FROM @xml.nodes('/DocumentElement/Stats')

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-28 : 00:48:23

try for openxml statements. it works

Iam a slow walker but i never walk back

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-28 : 02:11:28

Create Procedure sp_foo
(
@xml xml
)
AS
INSERT INTO dbo.VMWARE (Entity, [Type], [Time], [Metric], [AVG], [MAX], [MIN])
SELECT
doc.col.value('Entity[1]', 'varchar(255)') Entity,
doc.col.value('Type[1]', 'varchar(255)') [Type],
doc.col.value('Time[1]', 'datetime') [Time],
doc.col.value('Metric[1]', 'varchar(255)') [Metric],
doc.col.value('AVG[1]', 'int') [AVG],
doc.col.value('MAX[1]', 'int') [MAX],
doc.col.value('MIN[1]', 'int') [MIN]
FROM @xml.nodes('/DocumentElement/Stats')

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-28 : 03:05:44

It seems that the posted data is not in clear to understand,
Can you post some sample xml data

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-28 : 04:38:11

Check this is this u want
some sample data for example
DECLARE @table TABLE (parentnodename VARCHAR(12), NodeName VARCHAR(12),NodeText VARCHAR(32))
DECLARE @r XML

SET @r = '
<Reference>
<Basic>
<Book>A1.Hj1.JU9</Book>
</Basic>
<App>
<A>AK9.HL9.J0</A>
<A>A18.H.PJ69</A>
</App>

B13.H98.P9
B18.HO9.JIU8

<DI>
<D>D23.HYT.P6R</D>
</DI>
</Reference>
'
INSERT INTO @table
SELECT t.n.value('local-name(..)[1]', 'VARCHAR(100)') AS ParentNodeName,
t.n.value('local-name(.)[1]', 'VARCHAR(100)') AS NodeName,
t.n.value('text()[1]', 'VARCHAR(100)') AS NodeText
FROM @r.nodes('/*/*/*') AS t(n)

SELECT * FROM @table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources