How to get XML block number with SQL Server? [duplicate]

Question

I have a XML document:

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

So here I want to get a list of x and y. But I want to enumerate contour blocks. I mean:

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

Now I can get list of x and y:

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)

But how can I enumerate contours blocks?

Have you tried to split your nodes() queries to land on contours/contour? You could probably then use dense_rank() over (order by contour) to get the desired numbers. e.g.: db<>fiddle — AlwaysLearning
– AlwaysLearning, Commented Jun 3, 2024 at 9:42
@AlwaysLearning dense_rank() is an exactly what i want. Works perfectly. If you add it as an answer I'll accept it. — Kliver Max
– Kliver Max, Commented Jun 3, 2024 at 13:53

jdweng · Accepted Answer · 2024-06-03 09:32:45Z

I would recommend using a Powershell cmdlet. You can use Invoke-SQLCmd (https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) to insert data into the database. Below is the code to parse the XML

using assembly System.Xml.Linq
$input_filename = 'c:\temp\test.xml'

$doc = [System.Xml.Linq.XDocument]::Load($input_filename)
$spatials_elements = $doc.Descendants('spatials_elements')

$table = [System.Collections.ArrayList]::new()

$contour = 0
foreach($spatials_element in $spatials_elements)
{
   $contour++
   foreach($ordinate in $spatials_element.Descendants('ordinate'))
   {
      $x = $ordinate.Element('x').Value
      $y = $ordinate.Element('y').Value
      $newRow = [pscustomobject]@{
       'contour' = $contour
       'x' = $x
       'y' = $y
      }
      $table.Add($newRow) | out-null
   }
}
$table

Here is the results

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

Collectives™ on Stack Overflow

How to get XML block number with SQL Server? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related