Hi - I'm using SQL Server 2005, and I have a view with an XML data type column. I would like to write a stored proc that takes an XML data type as a parameter and return the rows from the view that match the columns in the parameter.
At this point, I'm just trying to get the syntax correct, so I started with some simple queries. Below are the queries:
declare @.params xml
declare @.view xml
set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'
set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'
SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c)
I would like to get the nodes from @.view where @.view.hierarchyTypeId = @.params.hierarchyTypeId. This should be pretty simple, but I'm missing it...
Any thoughts are appreciated!
Thanks,
Phil
It sounds like you are trying to join view and params to get a results set which has one matching node per row. If that is the case, the below query should be doing what you are looking for. If you want to return a single xml fragment, a different technique would have to be used.
In the query below, we iterate over the HierarchyType nodes of both @.view and @.params, project the values of the hierarchyTypeId attributes as integers using the the value() function, verify that they match, and the return the @.param node that matches.
declare @.params xml
declare @.view xml
set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
</HierarchyTypes>'
set @.view = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>'
SELECT T.c.query('.') AS result
FROM @.view.nodes('/HierarchyTypes/HierarchyType') T(c),
@.params.nodes('/HierarchyTypes/HierarchyType') P(c)
WHERE T.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')
Hi Todd - this is very close to what I need. The only other wrinkle is that I'm trying to select all of the columns from the view (not just the hierarchyTypeId). The view has an XML column called HierarchyTypes, and I want to use that column in my 'join criteria'. And I'd like to do this in a stored proc.
CREATE PROCEDURE [dbo].[CodeHierarchy_SearchHierarchy]
@.codeTypes xml = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT v.nodeId,
v.parentNodeId,
v.HierarchyTypes
FROM VLinkedCodeHierarchies as v
WHERE /* hierarchyTypes in @.codeTypes are also in v.HierarchyTypes */
At this point, I also require that the return is NOT XML.
Thanks,
Phil
|||So it sounds like you have a table or a view that has a few relational columns, and 1 xml column. And then you want to join it with an XML fragment.
Is that correct?
If so, then you can modify the query to use CROSS APPLY. In the example I create a test table which has an xml column, fill it with data and then join it with the fragment, projecting the relevant relational columns and xml data. You could then wrap it in a stored procedure or user defined function as needed.
(I CROSS APPLY the vTest table with nodes() function so that I get the nodes for the current row.)
declare @.params xml
drop table vTest
Create table vTest(
id int,
val xml
)
insert into vTest (id, val) values (1,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" otherProp = "1"/>
<HierarchyType hierarchyTypeId="5" otherProp = "2"/>
<HierarchyType hierarchyTypeId="6" otherProp = "3"/>
<HierarchyType hierarchyTypeId="7" otherProp = "4"/>
</HierarchyTypes>')
insert into vTest (id, val) values (2,'<HierarchyTypes>
<HierarchyType hierarchyTypeId="8" otherProp = "1"/>
<HierarchyType hierarchyTypeId="9" otherProp = "2"/>
<HierarchyType hierarchyTypeId="10" otherProp = "3"/>
<HierarchyType hierarchyTypeId="11" otherProp = "4"/>
</HierarchyTypes>')
set @.params = '<HierarchyTypes>
<HierarchyType hierarchyTypeId="4" />
<HierarchyType hierarchyTypeId="5" />
<HierarchyType hierarchyTypeId="11" />
</HierarchyTypes>'
SELECT vTable.Id,
xVal.c.value('@.hierarchyTypeId', 'int') as hierarchyTypeId,
xVal.c.value('@.otherProp', 'int') as otherProp,
xVal.c.query('.') as MatchingFragment
FROM @.params.nodes('/HierarchyTypes/HierarchyType') P(c),
vTest as vTable CROSS APPLY vTable.Val.nodes('/HierarchyTypes/HierarchyType') xVal(c)
WHERE xVal.c.value('@.hierarchyTypeId', 'int') = P.c.value('@.hierarchyTypeId', 'int')
If you dont want to Join the parameter fragment and the table, but just want to check that for each row it's xml column has some data in common with the parameter fragment, then you could change the query to use an EXISTS.
|||
Hi Todd - thanks again for the excellent reply. I actually want to make sure that the rows returned have data in common with the XML fragment.
Ideally, I would like to have 3 options:
1) View column has some data from XML fragement
2) View column has all data from XML fragment, but could have more.
3) View column has ONLY data from XML fragement.
I can accomplish #1 above using a SELECT DISTINCT, but I don't think that's the most optimum.
Where are some good resources to educate myself on this?
Thanks!
Phil
|||Here is where you can find the the basics of XQuery and the T-SQL functions that support it:
http://msdn2.microsoft.com/en-us/library/ms190262.aspx
The current w3c resources:
http://www.w3.org/TR/xquery/
T-SQL Reference:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx
And there are probably a number of tutorials that would discuss the differences of when to use a DISTINCT vs EXISTS vs CROSS APPLY.
