Stuck on some 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 2000 Forums

SQL Server Development (2000)

Stuck on some XML

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-06 : 06:56:56

Hi All,

I am importing some xml and I'd like to get it into a specific format in a table. I'm loosing it !

so here's what I'm after...


'<root>
	<termlyfees census_return_item_id="-1" census_return_parent_id="">
		<termly_fees_day_min census_return_item_id="-2" census_return_parent_id="-1">100.3</termly_fees_day_min>
		<termly_fees_day_max census_return_item_id="-3" census_return_parent_id="-1">399.2</termly_fees_day_max>
		<termly_fees_boarding_min census_return_item_id="-4" census_return_parent_id="-1">10.2</termly_fees_boarding_min>
		<termly_fees_boarding_max census_return_item_id="-5" census_return_parent_id="-1">93.1</termly_fees_boarding_max>
	</termlyfees>
</root>'



element_name           element_id  parent_id 
---------------------- ----------- -----------
"termly_fees"          "-1"        ""
"termly_fees_day_min"  "-2"        "-1"
"termly_fees_day_max"  "-3"        "-1"

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2005-10-07 : 04:52:04

I'm pretty sure this isn't possible so have changed direction. Thanks to those who took a look.

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 06:00:48

I did wonder if you could get what you wanted using "edge format", but I didn't have time to try it


declare @idoc int
declare @doc varchar(8000)
set @doc =
'<root>
	<termlyfees census_return_item_id="-1" census_return_parent_id="">
		<termly_fees_day_min census_return_item_id="-2" census_return_parent_id="-1">100.3</termly_fees_day_min>
		<termly_fees_day_max census_return_item_id="-3" census_return_parent_id="-1">399.2</termly_fees_day_max>
		<termly_fees_boarding_min census_return_item_id="-4" census_return_parent_id="-1">10.2</termly_fees_boarding_min>
		<termly_fees_boarding_max census_return_item_id="-5" census_return_parent_id="-1">93.1</termly_fees_boarding_max>
	</termlyfees>
</root>'

exec sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT    *
FROM       OPENXML (@idoc, '/root')
EXEC sp_xml_removedocument @idoc

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources