SQL Server中计算LandXML内CoordGeom段@length的累计值

To compute the cumulative SegmentStartM and SegmentEndM values for each CoordGeom segment (Line, Curve, Spiral) in your LandXML data, use this XQuery approach leveraging sequence position tracking and numeric operations:

Step-by-Step Implementation #

1. Gather Target Segments: Collect all relevant segment nodes into a sequence variable for easy reference.
2. Track Segment Position: Use the position of each segment in the sequence to calculate sums of preceding segments' lengths.
3. Cast to Numeric: Convert string-based @length attributes to numeric types to ensure valid arithmetic calculations.
4. Calculate Cumulative Values:
   • SegmentStartM: Sum of lengths of all segments before the current one
   • SegmentEndM: Sum from SegmentStartM plus the current segment's length

Example Query #

Assume your LandXML data is stored in a table LandXMLData with an XML column XmlContent. Adjust the namespace if your XML uses a different schema:

SELECT
    XmlContent.query('
        declare namespace landxml="http://www.landxml.org/schema/LandXML-1.2";
        let $segments := /landxml:LandXML/landxml:Alignments/landxml:Alignment/landxml:CoordGeom/(landxml:Line | landxml:Curve | landxml:Spiral)
        for $seg at $pos in $segments
        let $startM := sum($segments[position() < $pos]/@length/xs:double(.))
        let $endM := $startM + xs:double($seg/@length)
        return
            <Segment>
                <SegmentType>{local-name($seg)}</SegmentType>
                <SegmentStartM>{$startM}</SegmentStartM>
                <SegmentEndM>{$endM}</SegmentEndM>
                <SegmentLength>{$seg/@length}</SegmentLength>
            </Segment>
    ') AS CumulativeSegmentData
FROM LandXMLData;

Key Details #

• Namespace Handling: The declaration matches the standard LandXML 1.2 schema. Remove or modify this line if your XML uses no namespace or a different one.
• Numeric Casting: xs:double(.) converts string @length values to numbers, avoiding invalid sum operations.
• First Segment Behavior: The first segment will have SegmentStartM = 0 (no preceding segments), which is the correct starting point.

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