July 22, 2008 at 4:58 am
Hi Friends,
I would like to get the data from the column from [HumanResources].[JobCandidate].Resume field in AdventureWorks DB. The desired Data is from "Emp.OrgName" node of JobCandidateId=3. Could anybody help me. Thanks in advance for your help.
Here i am trying some like this....
declare @x xml
Select @x= Resume from [HumanResources].[JobCandidate]
WHERE JobCandidateID = 3
--select @x
SELECT myResume.xxx.value('.','varchar(max)')as OrgName
FROM @x.nodes('/Resume/Employment/Emp.OrgName') myResume (xxx)
Also Do we need to take the XML content into one XML variable as i did above with @x ? or is there any direct way ??
July 22, 2008 at 7:44 am
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
SELECT myResume.xxx.value('.','varchar(max)')as OrgName
FROM [HumanResources].[JobCandidate] J CROSS APPLY J.[Resume].nodes('/Resume/Employment/Emp.OrgName') myResume (xxx)
WHERE J.JobCandidateID = 3
July 23, 2008 at 2:58 am
working fine. I read SQL Books online to understand what is this CROSS/OUTER APPLY. What i understood from the books online is CROSS/OUTER APPLY are used for "to iterate on rows from both sides of the CROSS/OUTER APPLY operator. which we can do using normal INNER JOIN/OUTER JOIN as long as the left and right side results are directly from table. But inner/outer joins wont work if one of the input to these joins is from a UDF(User Defined Function which is returning result set). Tere fore this CROSS/OUTER APPLY are alternative to INNER/OUTER JOIN respectively. But in your query you are using CROSS APPLY, Now my doubt is this CROSS APPLY will match left side records to right side records( As per definition of CROSS/OUTER APPLY). In your case on which field the comparison( Match) takes place ? I hope i expressed my doubt clearly 🙂
Example:
SELECT A.* from TableA A inner join dbo.myUDF(10) B ON A.ID = B.ID ;
where dbo.myUDF(10) is a function call with int parameter 10 returns table value.
the alternative is "SELECT A.* from TableA A CROSS APPLY dbo.myUDF(10) B"
Correct ? YES it is correct. here also the join takes place on which field? how does this cross apply know that join (matching) should takes place on ID field from both of the Tables (TableA, dbo.myUDF(10) ) ?????
Please clarify my doubt .
Thanks in advance for your help.
Venkat.
July 23, 2008 at 6:43 am
CROSS APPLY is used to "JOIN" a table (A) with the second rowset, which is produced by a rowset function (TVF, xml nodes, etc) by taking A's columns as parameters. One row in table A makes a call to the rowset function, which can return more than one row. So it's a one to many relation. The relation between the two rowsets are matched by each row in the table A with all the output of the function by taking A's that row as parameter.
In the xquery, the right side nodes function takes the value of[Resume] column of each row in the left as parameter. So each row in the right is matched to its output of xml nodes function.
If the xml nodes function does not return any rows, the right side row eliminated. If you want to keep it in the return, you need to use OUTER APPLY.
In your CROSS APPLY example, dbo.myUDF(10) is called for each row in the TableA A though your UDF does not take parameter from the Table A. Each row in table A maches to all the rows of the return of dbo.myUDF(10) (one to many relation)
(the word CROSS in the CROSS APPLY sounds a little bit confusing)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply