SQL Server中的表的分层XML

I'm trying to convert data streaming from a medical device into a usable table. The device spits out XML-formatted text via an RS-232 port, which I've captured using a software called ComCap4 from Magenta Systems. I've been able to convert the one row per line of text into an XML object, but am getting turned around by the XML syntax in SQL Server.

我正在尝试将数据流从医疗设备转换为可用的表格。该设备通过RS-232端口发出XML格式的文本，我使用Magenta Systems的一个名为ComCap4的软件捕获了该端口。我已经能够将每行文本的一行转换为XML对象，但是我在SQL Server中的XML语法中得到了转换。

Here's an example of the XML:

这是XML的一个例子：

<sample>
  <instrinfo>
    <p>
      <n>SNO</n>
      <v>179</v>
    </p>
  </instrinfo>
  <smpinfo>
    <p>
      <n>ID</n>
      <v>26551.0</v>
    </p>
  </smpinfo>
  <smpresults>
    <p>
      <n>WBC</n>
      <v>8.4</v>
      <l>3.5</l>
      <h>10.0</h>
    </p>
  </smpresults>
</sample>
<sample>
  <instrinfo>
    <p>
      <n>SNO</n>
      <v>179</v>
    </p>
  </instrinfo>
  <smpinfo>
    <p>
      <n>ID</n>
      <v>26552.0</v>
    </p>
  </smpinfo>
  <smpresults>
    <p>
      <n>WBC</n>
      <v>6.1</v>
      <l>3.5</l>
      <h>10.0</h>
    </p>
  </smpresults>
</sample>

I'd like to convert this XML into a table that looks like:

我想将此XML转换为一个表格，如下所示：

InstrumentSNO  SampleID  WBCVal  WBCLow  WBCHigh
-------------  --------  ------  ------  -------
179            26551.0   8.4     3.5     10.0
179            26552.0   6.1     3.5     10.0

I've had very little experience parsing XML (probably obvious from this question) and the examples I've found here and elsewhere leave me more confused than ever.

我解析XML的经验很少（这个问题可能很明显），我在这里和其他地方发现的例子让我比以往更加困惑。

Any help, links, or suggestions in the right direction are greatly appreciated!

任何有关正确方向的帮助，链接或建议都非常感谢！

1 个解决方案

#1

Try it like this:

试试这样：

DECLARE @xml XML=
'<sample>
  <instrinfo>
    <p>
      <n>SNO</n>
      <v>179</v>
    </p>
  </instrinfo>
  <smpinfo>
    <p>
      <n>ID</n>
      <v>26551.0</v>
    </p>
  </smpinfo>
  <smpresults>
    <p>
      <n>WBC</n>
      <v>8.4</v>
      <l>3.5</l>
      <h>10.0</h>
    </p>
  </smpresults>
</sample>
<sample>
  <instrinfo>
    <p>
      <n>SNO</n>
      <v>179</v>
    </p>
  </instrinfo>
  <smpinfo>
    <p>
      <n>ID</n>
      <v>26552.0</v>
    </p>
  </smpinfo>
  <smpresults>
    <p>
      <n>WBC</n>
      <v>6.1</v>
      <l>3.5</l>
      <h>10.0</h>
    </p>
  </smpresults>
</sample>';

--Here is the query

- 这是查询

SELECT s.value('(instrinfo/p/v)[1]','int') AS InstrumentSNO
      ,s.value('(smpinfo/p/v)[1]','decimal(10,1)') AS SampleID
      ,s.value('(smpresults/p/v)[1]','decimal(10,1)') AS WBCVal
      ,s.value('(smpresults/p/l)[1]','decimal(10,1)') AS WBCLow
      ,s.value('(smpresults/p/h)[1]','decimal(10,1)') AS WBCHigh
FROM @xml.nodes('/sample') AS A(s)

The result

结果

InstrumentSNO   SampleID    WBCVal  WBCLow  WBCHigh
179             26551.0     8.4     3.5     10.0
179             26552.0     6.1     3.5     10.0

Short explanation

Your XML contains - obviously - sets of sample element. With CROSS APPLY .nodes('/sample') we can address them one by one.

您的XML包含 - 显然 - 包含样本元素。使用CROSS APPLY .nodes（'/ sample'），我们可以逐个解决它们。

Although there are nested elements, all of them seem to be 1:1 named elements, so we can just pick them by their names with a relativ path down from the sample-node.

尽管存在嵌套元素，但它们似乎都是1：1命名元素，因此我们可以通过名称从样本节点向下选择相对路径来选择它们。