只选择名称相同的几个节点

I'm trying to construct a soap message, and I was able to construct the entire message using a single select. Except the problem is, on only a few occasions the same node name is repeated twice.

我正在尝试构造一个soap消息，并且我能够使用一个select来构造整个消息。但是问题是，在少数情况下相同的节点名会重复两次。

So for example the required output result should be like so, with two separate id root nodes:

因此，例如，需要的输出结果应该是这样的，有两个独立的id根节点:

<SoapDocument>
  <recordTarget>
    <patientRole>
      <id root="1.2.3.4" extension="1234567" />
      <id root="1.2.3.5.6" extension="0123456789" />
    </patientRole>
  </recordTarget>
</SoapDocument>

I tried to use my sparse knowledge of xpath to construct the node names like so:

我尝试使用我的xpath知识来构造节点名，如下所示:

select
    '1.2.3.4'    AS 'recordTarget/patientRole/id[1]/@root',
    '1234567'    AS 'recordTarget/patientRole/id[1]/@extension',
    '1.2.3.5.6'  AS 'recordTarget/patientRole/id[2]/@root',
    '0123456789' AS 'recordTarget/patientRole/id[2]/@extension'
FOR XML PATH('SoapDocument'),TYPE

Apparently xpath naming can't be applied to column names id[1] and id[2] like that? Am I missing something here or should the notation be different? What would be the easiest way to constuct the desired result?

显然xpath命名不能应用于id[1]和id[2]这样的列名?我在这里漏掉了什么还是符号不同?什么是最简单的方法来确定想要的结果?

3 个解决方案

#1

From your question I assume, this is not tabular data, but fixed values and you are creating a medical document, assumably a CDA.

根据你的问题，我认为这不是表格数据，而是固定的值，你在创建一个医疗文档，假设是CDA。

Try this:

试试这个:

SELECT 
(
    SELECT
        '1.2.3.4'    AS 'id/@root',
        '1234567'    AS 'id/@extension',
        '',
        '1.2.3.5.6'  AS 'id/@root',
        '0123456789' AS 'id/@extension'
    FOR XML PATH('patientRole'),TYPE
) AS [SoapDocument/recordTarget]
FOR XML PATH('')

The result:

结果:

<SoapDocument>
  <recordTarget>
    <patientRole>
      <id root="1.2.3.4" extension="1234567" />
      <id root="1.2.3.5.6" extension="0123456789" />
    </patientRole>
  </recordTarget>
</SoapDocument>

Some explanation: The empty element in the middle allows you to place two elements with the same name in one query. There are various approaches how you get this into your surrounding tags. This is just one possibility.

一些说明:中间的空元素允许在一个查询中放置两个名称相同的元素。有很多种方法可以让你把它放到你周围的标签中。这只是一种可能性。

UPDATE

I'd like to point to BdR's own answer! Great finding and worth an up-vote!

我想指出BdR自己的答案!很棒的发现，值得一投!

#2

A little more elaboration on the answer from Shnugo, as it got me trying out some things using an "empty column".

对Shnugo的答案进行了更详细的阐述，因为它让我尝试了一些使用“空列”的东西。

If you do not give the emtpy column a name, it will reset to the XML root node. So the following columns will start from the XML root of the selection you are in at that point. However, if you explicitly name the empty separator column, then the following columns will continue in the hierarchy as set by that column name.

如果不给emtpy列一个名称，它将重置为XML根节点。因此，下面的列将从您所处的选择的XML根开始。但是，如果您显式地命名空分隔符列，那么下面的列将在该列名称所设置的层次结构中继续。

So the selection below will also result in the desired result. It's subtly different, but in my case it allows me to avoid using subselections.

所以下面的选择也会得到想要的结果。它有微妙的不同，但在我的例子中，它允许我避免使用子选择。

select
    '1.2.3.4'    AS 'recordTarget/patientRole/id/@root',
    '1234567'    AS 'recordTarget/patientRole/id/@extension',
    ''           AS 'recordTarget/patientRole',
    '1.2.3.5.6'  AS 'recordTarget/patientRole/id/@root',
    '0123456789' AS 'recordTarget/patientRole/id/@extension'
FOR XML PATH('SoapDocument'),TYPE

#3

This should do the job:

这应该可以做到:

WITH CTE AS (
    SELECT *
    FROM (VALUES('1.2.3.4','1234567'),
                ('1.2.3.5.6','0123456789')) V ([root], [extension]))
SELECT (SELECT (SELECT (SELECT [root] AS [@root],
                               [extension] AS [@extension]
                        FROM CTE
                        FOR XML PATH('id'), TYPE)
                FOR XML PATH('patientRole'), TYPE)
        FOR XML PATH ('recordTarget'), TYPE)
FOR XML PATH ('SoapDocument');