xml openxml insert help - 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 2000 Forums

Transact-SQL (2000)

xml openxml insert help

Author

Topic

jgn1013
Starting Member

24 Posts

Posted - 2009-01-13 : 15:47:03

I have done openxml using stored procedure before, but this xml file is a little different. Trying to create a table and stored procedure for this but am not sure how to strat. I've seen examples of openxml like :

<root>
<jobs>
<fname>John</fname>
<lname>DOe</lname>
</jobs?
</root>
insert into table
SELECT * FROM OPENXML(@intPointer,'/root/job',2)
**************************************************************
my new xml file is totaly different:
<xml_response attribute="here">
<et_get_next_trans request_id="1195133906828">
<usv_transmission id="33076">
<employee_record employer_cd="0000"
<emp_info>
<first_nm>JOHN</first_nm>
<address>
<address_1>1405 PEACHTREE RD</address_1>
<address_2 />
</address>
<phone />
<email />
</emp_info>
<dhs>
<case_num />
</dhs>
</employee_record>
</usv_transmission>
</et_get_next_trans>
</xml_response>

*******************************************************
I'm a novice with xml so not really sure where to start, if you could point me in the right direction that would be great.

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 13:08:46

what are values you want to extract?

jgn1013
Starting Member

24 Posts

Posted - 2009-01-14 : 13:18:13

all of them, i would really like to use openxml with stored procedure, but it's gotten a little complicated
here is what i've gotten so far, but I can't get anything indside the <address> </address> or get to the <dhs> </dhs> part.

select * from openxml
(@intPointer, '/xml_response/et_get_next_trans/usv_transmission/employee_record/emp_info',2)
with (request_id nvarchar(50) '../../../../@request_id', code nvarchar(50) '../../../../@code',
result_msg nvarchar(50) '../../../../@result_msg',
id nvarchar(50) '../../../@id', employer_cd nvarchar(50) '../../@employer_cd',
ssn nvarchar(50) '../../@ssn', event nvarchar(50)'../../@event',
first_nm nvarchar(50), mi nvarchar(50) , last_nm nvarchar(50), phone nvarchar(50), email nvarchar(50), address_1 nvarchar(50) '//address/address_1')

I'm thinking about creating a couple of tables, 1 for the general information, the other will have the <dhs /> information. The other error i'm getting is when i add this openxml throws asn error:
"XML parsing error: End element was missing the character '>'."

<address>
<address_1> somewhere</address_1>
<address_2 />
</address>

jgn1013
Starting Member

24 Posts

Posted - 2009-01-14 : 14:52:22

ok i figured out how to access the
<address_1>somewhere</address_1> using xpath attributes '//address/address_1'

now if i can only get rid of this error
"XML parsing error: End element was missing the character '>'"

I figured out the above error, declared value did not allow for enough characters.

edit:
declare @doc varchar(8000)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 09:10:55

ok..so have you suceeded in extracting all values?

jgn1013
Starting Member

24 Posts

Posted - 2009-01-15 : 09:14:09

I think so, I'm getting closer. Wondering if there is an easier way though?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 11:25:16

quote:
Originally posted by jgn1013

I think so, I'm getting closer. Wondering if there is an easier way though?

What is your final solution then?

jgn1013
Starting Member

24 Posts

Posted - 2009-01-15 : 11:46:52

I'm not completely done, the xml file that i'm getting is a little more complicated than the above mention:
here is the xml file that i'm getting:
******************************************************************************
<xml_response attribute="here">
<et_get_next_trans request_id="1195133906828">
<u_t id="33076">
<empl_record employer_cd="0000">
<emp_info>
<first_nm>JOHN</first_nm>
<address>
<address_1>1405 PEACHTREE RD</address_1>
<address_2 />
</address>
<phone />
<email />
</emp_info>
<dhs>
<case_num />
</dhs>
************new section here **************************
********************************************************
<forms>
<form name="19">
<field>
<name>first_nm</name>
<value>John</value>
</field>
</form>
</forms>
********************************************************
********************************************************
</employee_record>
</us_t>
</et_get_next_trans>
</xml_response>
**********************************************************
**********************************************************
*********************************************************

here is my openxml stored procedure:
SELECT * FROM OPENXML(@intPointer, '/xml_response/et_get_next_trans/u_t/empl_record',2)
WITH (request_id nvarchar(50) '../../../@request_id', id nvarchar(50) '../../@id',
employer_cd nvarchar(50) '../@employer_cd', first_nm varchar(20) 'emp_info/first_nm',
address_1 varchar(50) 'emp_info/address/address_1', address_2 varchar(50) 'emp_info/address/address_2',
case_nm varchar(20) 'dhs/case_nm')

********************************************************

The last problem is the new section:
<forms>
<form attr="1">
<field>
<name>First_nm</name>
<value>John</value>
</form>
</forms>

*************************************************

I'm thinking for this last section, I will load it into a datatable , then do an insert into sql. ???

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 16:00:12

You mean this .Right!!!


DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
<xml_response attribute="here"> 
<et_get_next_trans request_id="1195133906828"> 
<u_t id="33076"> 
<empl_record employer_cd="0000"> 
<emp_info> 
<first_nm>JOHN</first_nm> 
<address> 
<address_1>1405 PEACHTREE RD</address_1> 
<address_2 /> 
</address> 
<phone /> 
<email /> 
</emp_info> 
<dhs> 
<case_num /> 
</dhs> 
<forms> 
<form name="19"> 
<field> 
<name>first_nm</name> 
<value>John</value> 
</field> 
</form> 
</forms> 
</empl_record> 
</u_t> 
</et_get_next_trans> 
</xml_response> '


EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM 
OPENXML(@idoc, '/xml_response/et_get_next_trans/u_t/empl_record',2)
WITH (request_id nvarchar(50) '../../@request_id', 
      id nvarchar(50) '../@id',
      employer_cd nvarchar(50) '@employer_cd', 
      first_nm varchar(20) 'emp_info/first_nm', 
      address_1 varchar(50) 'emp_info/address/address_1', 
      address_2 varchar(50) 'emp_info/address/address_2',
      case_nm varchar(20) 'dhs/case_nm')


EXEC sp_xml_removedocument @idoc

jgn1013
Starting Member

24 Posts

Posted - 2009-01-15 : 17:40:57

yes, i must have take the other one from one of my other test procedure. thanks again for all the help! I will try to put it all together tomorrow.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 17:51:18

Welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources