问题描述
我试图将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函数不会按表的给定顺序插入值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!