存储proc中的SQL Server 2005 XML查询

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

我有一个存储的proc，它接受这样的xml值输入:

<?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" 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>

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

我需要将一些表连接到此数据，并用DB数据填充name=""属性。

How do I go about this in SQL Server 2005?

我如何在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

在最坏的情况下，我认为我可以将XML解析为这两种类型(project & milestone)中的每一种临时表，然后连接到其中，然后使用灵巧的sql为XML选择我的数据

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

或者至少我认为我应该，还没开始工作……

Any clues?

有线索吗?

2 个解决方案

#1

^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:

^,使用这个XQUery,您可以您的XML“分解”到pseudo-table(一行内每个< item >节点< RWFCriteria >)——现在你可以使用加入对其他表,没有问题:

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:

更新:好的，要根据临时表重新创建XML，需要如下内容:

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>).

(“项目”)定义的路径表中每一行的元素,根(RWFCriteria)应该是显而易见的,并通过指定“@ id”等列被选中,您定义这些被放入< item >——使用@让成< item >节点上的一个属性(没有@,他们会在 <项目> 元素)。

#2

-1

FOR XML is pretty powerful.

XML非常强大。

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.

从这里开始，JOIN等就很简单了。

#1