Importing XML into rows - Stored proc

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

Transact-SQL (2008)

Importing XML into rows - Stored proc

Author

Topic

misterish
Starting Member

10 Posts

Posted - 2011-12-14 : 18:58:58

Hello,
I am passing an XML string to a stored proc. The following stored proc works is my XML uses attributes in the nodes:


<data>
    <log user_id="1" activity_id="1" log_date="2011-12-14" log_actualdate="2011-12-14" log_actualtime="12:00:00"></log>
    <log user_id="1" activity_id="2" log_date="2011-12-14" log_actualdate="2011-12-14" log_actualtime="12:00:00"></log>
    <log user_id="1" activity_id="3" log_date="2011-12-14" log_actualdate="2011-12-14" log_actualtime="12:00:00"></log>
</data>

INSERT INTO dbo.log (
		user_id,
		activity_id,
		log_date,
		log_actualdate,
		log_actualtime
	)
		 
		SELECT 
			LogData.Item.value('@user_id', 'INT'), 
			LogData.Item.value('@activity_id', 'INT'),
			LogData.Item.value('@log_date', 'DATE'),
			LogData.Item.value('@log_actualdate', 'DATE'),
			LogData.Item.value('@log_actualtime', 'TIME(7)')
		FROM @XMLData.nodes('//data/log') AS LogData(Item)

But I would like my XML to be nested as such:


<data>
    <log>
        <user_id>1</user_id>
        <activity_id>1</activity_id>
        <log_date>2011-12-14</log_date>
        <log_actualdate>2011-12-14</log_actualdate>
        <log_actualtime>12:00:00</log_actualtime>
    </log>
    <log>
        <user_id>1</user_id>
        <activity_id>2</activity_id>
        <log_date>2011-12-14</log_date>
        <log_actualdate>2011-12-14</log_actualdate>
        <log_actualtime>12:00:00</log_actualtime>
    </log>
    <log>
        <user_id>1</user_id>
        <activity_id>3</activity_id>
        <log_date>2011-12-14</log_date>
        <log_actualdate>2011-12-14</log_actualdate>
        <log_actualtime>12:00:00</log_actualtime>
    </log>
</data>

How do I alter my stored proc for this format to be accepted?

Thank you!

Joe

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-14 : 19:35:55

Change each of the columns to pick up the node value rather than the attribute value; for example change this:

LogData.Item.value('@user_id', 'INT'),

LogData.Item.value('user_id[1]', 'INT'),

misterish
Starting Member

10 Posts

Posted - 2011-12-21 : 22:54:40

AHH! Thank you! I was so close with this:


LogData.Item.value('user_id', 'INT')

misterish
Starting Member

10 Posts

Posted - 2011-12-21 : 22:57:35

Part 2 of this process. I am sending XML to my stored proc to delete a single record, but this deletes every record. Any idea why?


XML:

<data>
    <log>
        <user_id>900</user_id>
        <activity_id>1</activity_id>
        <log_date>2011-12-14</log_date>
    </log>
</data>

STORED PROC:

DELETE FROM log
	WHERE EXISTS (
	SELECT 
		Data.Item.value('user_id[1]', 'INT'),
		Data.Item.value('activity_id[1]', 'INT'),
		Data.Item.value('log_date[1]', 'DATE')
	FROM @XMLData.nodes('//data/log') AS Data(Item)
	);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 04:20:28

[code]
DELETE l
FROM log l
INNER JOIN(
SELECT
Data.Item.value('user_id[1]', 'INT') AS user_id,
Data.Item.value('activity_id[1]', 'INT') as activity_id,
Data.Item.value('log_date[1]', 'DATE') as log_date
FROM @XMLData.nodes('//data/log') AS Data(Item)
)x
ON x.user_id = l.user_id
AND x.activity_id = l.activity_id
AND...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

misterish
Starting Member

10 Posts

Posted - 2011-12-22 : 11:17:40

Perfect! Thanks again!

Visakh, how much will you charge to teach me everything you know about SQL :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 11:45:39

quote:
Originally posted by misterish

Perfect! Thanks again!

Visakh, how much will you charge to teach me everything you know about SQL :)

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 12:35:11

what do you call this reference?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources