openxml query with insert statement

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 2000 Forums

Transact-SQL (2000)

openxml query with insert statement

Author

Topic

jgn1013
Starting Member

24 Posts

Posted - 2009-01-15 : 14:24:03

example:

<forms uniqueid='336'>
<field>
<name>f_name</name>
<value>John</value>
</field>

<field>
<name>l_name</name>
<value>Doe</value>
</field>

</forms>

***************************************************************
If i query this xml like this:

select * openxml(@pointer, '/form/field',2)
with (uniqueid int '@uniqueid', name varchar(20), valuevarchar(20))

I get this:

uniqueid..name...value
336..........f_name...John
336..........l_name...Dean
etc..

how would I add a select statement to get the query and insert it into a talbe that looks like this:

uniqueid....first_nm.....last_nm
336...........John...........Doe

I'm guessing some looping would be needed? help! TIA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 15:03:09

Like this:



DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<forms uniqueid= "336" >
<field>
<name>f_name</name>
<value>John</value>
</field>
<field>
<name>l_name</name>
<value>Doe</value>
</field>
</forms>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

Insert into Newtable
Select uniqueid,
Max(Case when [name] = 'f_name' then [value] else null end)as [First_nm],
Max(Case when [name] = 'l_name' then [value] else null end)as [Last_nm]
from
(Select *  from
openxml(@idoc, '/forms/field',2)
with (uniqueid int '../@uniqueid',
      [name] varchar(20), 
      [value] varchar(20)))x
Group by uniqueid

EXEC sp_xml_removedocument @idoc