I need to read value from geography value from xml string.
When i manually run below code, its working fine
select (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326))
select (geography::STGeomFromText('LINESTRING(-122.360 47.657, -122.343 47.657 )', 4326))
I need below like code
DECLARE @hdoc INT
DECLARE @xmlData xml = '<Root>
<Node>
<code>-122.360 47.656, -122.343 47.656</code>
</Node>
<Node>
<code>-122.360 47.657, -122.343 47.657</code>
</Node>
</Root>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XmlString
CREATE TABLE #temp
(
code [geography]
)
/**************************************INSERTION INTO TEMPORARY TABLE FROM XML**************************************/
INSERT INTO #temp
(
[code]
)
SELECT
code
FROM OPENXML (@hdoc, '/Root/Node', 2) WITH
(
[code] [geography]
-- select (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326))
-- How to read geography value from XML string
-- Here how to read dynamic value from XML
)
select * from #temp