如何在SQL Server中为XML的contour块编号并提取x、y坐标

问题描述 #

给定如下XML文档：

<build>
    <name>TestName</name>
    <contours>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
                <ordinate>
                  <x>578943.19</x>
                  <y>1403159.55</y>
                </ordinate>
                <ordinate>
                  <x>578935.03</x>
                  <y>1403159.09</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578935.46</x>
                  <y>1403152.82</y>
                </ordinate>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
    </contours>
  </build>

需要提取其中的x、y坐标，同时为每个<contour>块添加序号，期望输出如下：

contour x y
1 578943.62 1403153.28
1 578943.19 1403159.55
1 578935.03 1403159.09
1 578943.62 1403153.28
2 578935.46 1403152.82
2 578943.62 1403153.28

目前已实现坐标提取的SQL代码如下：

declare @xml xml

set @xml = '
    <build>
    <name>TestName</name>
    <contours>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
                <ordinate>
                  <x>578943.19</x>
                  <y>1403159.55</y>
                </ordinate>
                <ordinate>
                  <x>578935.03</x>
                  <y>1403159.09</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578935.46</x>
                  <y>1403152.82</y>
                </ordinate>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
    </contours>
  </build>
' 

select
    xml_ordinates.ordinate.value('(./x)[1]','decimal(20,2)') AS X
   ,xml_ordinates.ordinate.value('(./y)[1]','decimal(20,2)') AS Y
from @xml.nodes('/build') land (land)
    CROSS APPLY land.land.nodes('./contours/contour/entity_spatial/spatials_elements/spatial_element/ordinates/ordinate') xml_ordinates(ordinate)

需要修改上述代码，实现为每个<contour>块添加序号的需求。

解决方案 #

修改后的SQL代码如下：

declare @xml xml

set @xml = '
    <build>
    <name>TestName</name>
    <contours>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
                <ordinate>
                  <x>578943.19</x>
                  <y>1403159.55</y>
                </ordinate>
                <ordinate>
                  <x>578935.03</x>
                  <y>1403159.09</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
      <contour>
        <entity_spatial>
          <spatials_elements>
            <spatial_element>
              <ordinates>
                <ordinate>
                  <x>578935.46</x>
                  <y>1403152.82</y>
                </ordinate>
                <ordinate>
                  <x>578943.62</x>
                  <y>1403153.28</y>
                </ordinate>
              </ordinates>
            </spatial_element>
          </spatials_elements>
        </entity_spatial>
      </contour>
    </contours>
  </build>
' 

select
    contour_info.ContourSeq AS contour,
    ordinate_nodes.ordinate.value('(./x)[1]','decimal(20,2)') AS x,
    ordinate_nodes.ordinate.value('(./y)[1]','decimal(20,2)') AS y
from @xml.nodes('/build') land (land)
    CROSS APPLY land.land.nodes('./contours/contour') contour_nodes(contour)
    CROSS APPLY contour_nodes.contour.nodes('./entity_spatial/spatials_elements/spatial_element/ordinates/ordinate') ordinate_nodes(ordinate)
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ContourSeq
        FROM @xml.nodes('/build/contours/contour') AS seq_nodes(seq)
        WHERE seq_nodes.seq = contour_nodes.contour
    ) contour_info

关键说明 #

1. 先通过CROSS APPLY遍历每个<contour>节点，建立坐标节点与所属contour的关联。
2. 利用ROW_NUMBER()函数为每个<contour>生成递增序号，通过子查询匹配当前contour节点来获取对应的序号。
3. 最终输出时将序号、x、y坐标按需求格式返回。

内容的提问来源于stack exchange，提问作者Kliver Max