SQL Server 2005 XML query in stored proc

I have a stored proc that takes an input of xml value like this:

<?xml version="1.0" encoding="utf-16"?>
<RWFCriteria xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" reportType="Executive">
  <item id="44" n开发者_如何学Pythoname="" value="" type="Project" />
  <item id="45" name="" value="" type="Project" />
  <item id="46" name="" value="" type="Project" />
  <item id="110" name="" value="" type="Milestone" />
  <item id="111" name="" value="" type="Milestone" />
</RWFCriteria>

I need to join some tables to this data and populate the name="" attributes with DB data.

How do I go about this in SQL Server 2005?

At worst I think I can parse the XML into temp tables for each of the two types (project & milestone) and join to that then select out my data with a crafty sql using FOR XML

Or at least I think I should, have not gotten it to work yet...

Any clues?

^Well, using this XQUery, you can "shred" your XML into a pseudo-table (one row for each <item> node inside <RWFCriteria>) - which you could now use to join against other tables, no problem:

SELECT 
    RWF.Item.value('(@id)[1]', 'int') AS 'ID',
    RWF.Item.value('(@name)[1]', 'varchar(50)') AS 'Name',
    RWF.Item.value('(@type)[1]', 'varchar(50)') AS 'Type'
FROM
    @XmlVariableOrColumn.nodes('/RWFCriteria/item') AS RWF(Item)

Gives me an output of:

ID  Name    Type
44      Project
45      Project
46      Project
110     Milestone
111     Milestone

Update: OK, to re-create your XML, based on your temp table, you need something like this:

SELECT 
    id AS '@id',
    projectname AS '@name',
    VALUE AS '@value',
    type AS '@type'
FROM
        #tmp t
FOR XML PATH('item'), ROOT('RWFCriteria')

The PATH('item') defines the element for each row in your table, the ROOT('RWFCriteria') should be obvious, and by specifying AS '@id' etc. on your columns being selected, you define how those are being put into the <item> - using the @ makes them into an attribute on the <item> node (without the @, they'd be elements inside the <item>).

FOR XML is pretty powerful.

Assuming something like:

DECLARE @p_XmlData VARCHAR(MAX)
SELECT @p_XmlData = '<RWFCriteria xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" reportType="Executive">
  <item id="44" name="" value="" type="Project" />
  <item id="45" name="" value="" type="Project" />
  <item id="46" name="" value="" type="Project" />
  <item id="110" name="" value="" type="Milestone" />
  <item id="111" name="" value="" type="Milestone" />
</RWFCriteria>'

Here's a simple SELECT.

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @p_XmlData

    SELECT
        XMLData.id,
        1 AS [Version],
        XMLData.name,
        XMLData.value,
        XMLData.[type]
    FROM OPENXML (@hdoc, 'RWFCriteria/item', 1)
    WITH
    (
        id int,
        [name] varchar(256),
        [value] varchar(256),
        [type] varchar(256)
    ) AS XMLData

    EXEC sp_xml_removedocument @hDoc

From here, the JOIN etc is simple.