How to Insert Output of OPENXML to a Table

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)

How to Insert Output of OPENXML to a Table

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 05:18:12

I have an xml typed field containing column names and values and need to read value of each column and insert it into another table.
My code is as below

INSERT INTO tableName
	-- SELECT stmt using OPENXML rowset provider
		SELECT *
		FROM   OPENXML (@idoc, '/t',1)
				 WITH (ID  int,
					   FirstName  varchar(50) ,
					   LastName  varchar(50) ,
					   DateOfBirth  datetime )

but when I run it, nothing happens.
my XML value is as below:

<t><ID>14</ID><FirstName>c</FirstName><LastName>v</LastName><DateOfBirth>2002-01-01T00:00:00</DateOfBirth></t>

and my table is as below:

CREATE TABLE [dbo].[tableName](
	[ID] [int] NOT NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[DateOfBirth] [smalldatetime] NULL,
 CONSTRAINT [PK_tableName] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 05:30:33

You need to first prepare document for parsing and then use OPENXML

declare @test varchar(8000),@idoc int

set @test='<t><ID>14</ID><FirstName>c</FirstName><LastName>v</LastName><DateOfBirth>2002-01-01T00:00:00</DateOfBirth></t>'

EXEC sp_xml_preparedocument @idoc OUTPUT,@test
--INSERT INTO tableName
	-- SELECT stmt using OPENXML rowset provider
		SELECT *
		FROM   OPENXML (@idoc, '/t',2)
				 WITH (ID  int,
					   FirstName  varchar(50) ,
					   LastName  varchar(50) ,
					   DateOfBirth  datetime )

EXEC sp_xml_removedocument @idoc 

output
--------------------------
ID          FirstName                                          LastName                                           DateOfBirth
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
14          c                                                  v                                                  2002-01-01 00:00:00.000

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 05:48:54

Thanks a lot Visakh
My mistake was in:
OPENXML (@idoc, '/t',2)

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 06:56:25

Now I need to read just ID field value into a variable

Could you help me?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-25 : 06:59:13

DECLARE @id int

SELECT @id=id
FROM OPENXML (@idoc, '/t',2)
WITH (ID int,
FirstName varchar(50) ,
LastName varchar(50) ,
DateOfBirth datetime )

SELECT @id

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-25 : 07:16:21

Will you be having a single node in realitry or was it for illustration. if you've single node use Madhi's suggestion else yu need to use a temporary table to hold result as it will have more than one value.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 07:21:04

it works fine Madhivan
Thank you :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-25 : 08:28:15

you could also directly use the sql server 2005 built in XML functions like query(), nodes(), etc... since the column datatype is xml anyway. look them up in BOL.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 08:52:55

yes I'll modify my sql stmt
Thanks again

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-25 : 08:56:17

are you then deleting the inserted data from the MasterAuditTable after your trigger inserts then to correct audit tables?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 09:08:47

quote:
Originally posted by spirit1

are you then deleting the inserted data from the MasterAuditTable after your trigger inserts then to correct audit tables?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

You're tracing me well Spirit

Yes I've decided to delete the inserted data from MasterAuditTable to keep it small. Is it risky?!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-25 : 09:11:43

no it's not risky. so instead one huge table you'll have N less huge ones?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-25 : 09:31:18

yes :) I have to prepare those tables to offer them to our programmers to create view on them!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-25 : 09:51:52

views? why?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-26 : 02:12:47

They need a join of those tables in their application

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 03:44:46

quote:
Originally posted by Peace2007

They need a join of those tables in their application

only from getting data together you create view?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-26 : 07:01:55

quote:
Originally posted by visakh16
only from getting data together you create view?

I didn't get you, sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 07:10:02

quote:
Originally posted by Peace2007

quote:
Originally posted by visakh16
only from getting data together you create view?

I didn't get you, sorry

nope i was asking whether purpose of creating the view was whether just to join data from some tables?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-26 : 07:23:57

I'm not sure that's the case of developers,which I'm not involved with :) They've just asked me for being able to create view on those tables

alemos
Starting Member

16 Posts

Posted - 2008-10-06 : 18:15:26

I have run into a problem. I have this XML:


<nsml>
  <fields>
    <f id="PAGE">1</f>
    <f id="CATEGORY">abc</f>
    <f id="TITLE">test</f>
  </fields>
</nsml>

I need to find the value (test) of the TITLE node. This is my code:


DECLARE @idoc int
DECLARE @doc varchar(1000)
DECLARE @TITLE VARCHAR(100)
SET @doc ='
<nsml>
<fields>
    <f id="PAGE">1</f>
    <f id="CATEGORY">abc</f>
    <f id="TITLE">test</f>
</fields>
</nsml>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT    @TITLE = title
FROM       OPENXML (@idoc, '/nsml/fields/f',2)
WITH (title  varchar(10) '@id')
EXEC sp_xml_removedocument @idoc

But I get 'PAGE', 'CATEGORY' and 'TITLE' instead of the content. How can I get the content? Result of the query:


title
----------
PAGINA
CATEGORIA
TITLE

(3 row(s) affected)

Thanks everyone!

Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 00:19:56

DOnt hijack threads. please post your question as a new thread in future. Anyways seems like what you want is this

DECLARE @idoc int
DECLARE @doc varchar(1000)
DECLARE @TITLE VARCHAR(100)
SET @doc ='
<nsml>
<fields>
    <f id="PAGE">1</f>
    <f id="CATEGORY">abc</f>
    <f id="TITLE">test</f>
</fields>
</nsml>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT    @TITLE = title
FROM       OPENXML (@idoc, '/nsml/fields/f[@id=''TITLE'']',2)
WITH (title  varchar(10) '.')
EXEC sp_xml_removedocument @idoc
SELECT @TITLE


output
-----------------------
test

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources