存储过程中的OpenXml函数不会按表的给定顺序插入值 [英] OpenXml function in stored procedure won't insert values in given order to table

问题描述

我试图将X，Y坐标列表插入到RideOfferCoordinates表中，但是存储过程函数始终以相同的顺序插入它们，而不考虑它们的发送顺序.

I'm trying to insert a list of X,Y coordinates into a RideOfferCoordinates table but the stored procedure function inserts them always in the same order, regardless of the order they are sent in.

我将坐标作为xml字符串发送:

I send the coordinates as an xml string:

< Coordinates>
< row X="34.9116" Y="32.30498"/>
< row X="34.91151" Y="32.305420000000005"/>
< row X="34.85826" Y="32.328250000000004"/>
< row X="34.855790000000006" Y="32.32117"/>
< /Coordinates>

此sp函数:

ALTER PROCEDURE dbo.SaveRideOfferCoordinates
    (
    @rideOfferId Int,
    @coordinatesXml ntext
    )
AS

declare @idoc int;

exec sp_xml_preparedocument @idoc out, @coordinatesXml


SELECT X,Y

into #temp

from openxml(@idoc, '/Coordinates/row',1) 

  with (

         X real,

         Y real

       )



insert into RideOfferCoordinates select @rideOfferId, X, Y from #temp

drop table #temp

RETURN 0

这应该先将X="34.9116" Y="32.30498"插入表中，但是首先应插入X="34.855790000000006" Y="32.32117"，最后插入X="34.9116" Y="32.30498".

this should insert X="34.9116" Y="32.30498" first into the table, but instead it first inserts X="34.855790000000006" Y="32.32117", and X="34.9116" Y="32.30498" last.

我唯一的猜测是由于某种原因，sp根据X值以递增方式插入它们.

My only guess is that sp inserts them in an ascending fashion according to the X values for some reason..

推荐答案

为什么要按特定顺序使用它们?您是否需要按特定顺序在某些时候检索它们?您如何保证呢?

Why do you need them in a certain order? Do you need to retrieve them at some point in a certain order? How can you guarantee that?

如果需要订单"，则在表和传入数据中添加序号"列.

If you need "order", then add a "Ordinal" column to the table and to the incoming data.

我认为您需要担保"，而不是我希望如此".

I think you need a "guarantee", rather than a "I sure hope so".

EDIT _-----

EDIT_-----

如果您使用的是Sql Server 2005或更高版本，则应放弃OPENXML.

If you're using Sql Server 2005 or above........you should abandon OPENXML.

-- Declare XML variable

DECLARE @data XML;

-- Element-centered XML

SET @data = N'

<Coordinates>
<row X="34.9116" Y="32.30498" Ordinal="1" />
<row X="34.91151" Y="32.305420000000005" Ordinal="2" />
<row X="34.85826" Y="32.328250000000004" Ordinal="3" />
<row X="34.855790000000006" Y="32.32117" Ordinal="4" />
</Coordinates>

';




SELECT  
        T.childEntity.value('(@X)[1]', 'decimal(16,7)') AS XCoordinate
        ,       T.childEntity.value('(@Y)[1]', 'decimal(16,7)') AS YCoordinate
        ,       T.childEntity.value('(@Ordinal)[1]', 'int') AS MyOrdinal
FROM @data.nodes('Coordinates/row') AS T(childEntity)

这篇关于存储过程中的OpenXml函数不会按表的给定顺序插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！