September 3, 2008 at 3:09 am
DECLARE @X XML
SET @X = (SELECT XML
FROM @tempLogical)
INSERT INTO @tempPhysical ([MovementIdentifier])
SELECT CAST ( T.c.query('text()') AS CHAR(100) ) AS result
FROM @X.nodes('(/*[local-name()="Movement"]/*[local-name()="Relationship"][@Context="Movement.Physical"]/*[local-name()="Component"]/*[local-name()="Identifier"])') T(c)
im getting a 'Subquery returned more than 1 value Error' because there are two rows of xml in the @tempLogicalTable... how can i get this to work, occasionally i will get more than one record into the @tempLogical table. Basically this SQL is just stripping the id's from nodes in the xml and inserting those nodes into @tempPhysical any help appreciated......
September 3, 2008 at 3:22 am
What do you want your results to be if there more than one row?
Do you want to process each row or just one?
If you just want to process one then use TOP 1
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 3, 2008 at 3:23 am
i want to process each row....
September 3, 2008 at 6:21 am
You're trying to select multiple rows into a single variable. That just won't work. What about running that same XML query directly against the data rather than trying to move it into that variable? Or you could load the data into a temporary table with one column of data type XML?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2008 at 8:20 pm
I think you are going to need to use CROSS APPLY against your variable table. Without some sample XML and knowing what you have in your variable table it will be very hard to figure out what you need to do.
Gary Johnson
Sr Database Engineer
September 4, 2008 at 9:06 am
yip i used cross apply yesterday... works good, though hurts performance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply