June 9, 2011 at 9:42 am
Msg 174, Level 15, State 1, Line 6
The nodes function requires 1 argument(s).
I cant figure out was is wrong with this, does anyone know what is wrong with this ?
Declare @Drugs xml, @Offices xml, @BeginDateRange as Date, @EndDateRange as Date
SET @BeginDateRange = '2011/05/12'
SET @EndDateRange = '2011/05/15'
SET @Offices ='<Offices><OfficeID = "6"><Practice ID="7" /></officeid></Offices>'
SET @Drugs ='<Drugs><Product ID="6" /><Product ID="7" /><Product ID="8" /></Drugs>'
SELECT Offices.OfficeName, Practices.PracticeName, Products.ProductID
FROM Orders INNER JOIN
Practices ON Orders.PracticeID = Practices.PracticeID INNER JOIN
Offices ON Practices.PracticeID = Offices.PracticeID CROSS JOIN
Products
WHERE Practices.PracticeID in
(SELECT ParamValues.OfficeID.value('.','VARCHAR(20)') AS OfficeID, ParamValues.PracticeID.value('.','VARCHAR(20)') AS PracticeID
FROM @Offices.nodes('/Offices/OfficeID','/Offices/OfficeID/PracticeID') as ParamValues(OfficeID, PracticeID) )
and Products.ProductID in
(SELECT T.c.value('@ID', 'integer') As result FROM @Drugs.nodes('/Drugs/Product') as T(c))
and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange)
June 9, 2011 at 10:06 am
@Offices.nodes('/Offices/OfficeID', '/Offices/OfficeID/PracticeID')
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 1:28 pm
Here's a hint:
SELECT ParamValues.Practices.value('../@ID','VARCHAR(20)') AS OfficeID,
ParamValues.Practices.value('@ID','VARCHAR(20)') AS PracticeID
FROM @Offices.nodes('/Offices/Office/Practice') as ParamValues(Practices)
Eddie Wuerch
MCM: SQL
June 9, 2011 at 1:56 pm
Thanks eddie '../@ID' was what wasnt clicking, thanks so much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply