Re: Problem with OPENXML

In your front end program open the file and do a read into a buffer then use
that buffer as the parameter you pass into the stored procedure that has the
OpenXML statement. Exactly how you do that will depend on which language
you write your program in..

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Ivan" <ivan@xxxxxxxxxxxxx> wrote in message
news:OQMjKV9LHHA.420@xxxxxxxxxxxxxxxxxxxxxxx
How can I load the text file to the memory?

It's very frustrate to me now.

Ivan

"Roger Wolter[MSFT]" <rwolter@xxxxxxxxxxxxxxxxxxxx>
¼¶¼g©ó¶l¥ó·s»D:uQPfctyIHHA.1252@xxxxxxxxxxxxxxxxxxxxxxx
In SQL Server 2000 you would have to do that in a front end program -
load the file into memory and pass it as a text parameter In SQL Server
2005 the OpenRowSet Bulk command can load a file into a blob variable..

--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Alain Quesnel" <alainsansspam@xxxxxxxxxxxx> wrote in message
news:uSAKvpwIHHA.3668@xxxxxxxxxxxxxxxxxxxxxxx
Thanks, it works now.

Is it possible to load the XML from a file on disk instead of putting
the text into a SQL script? Something like
LoadFromFile('c:\temp\test.xml', @doc) ?

--

Alain Quesnel
alainsansspam@xxxxxxxxxxxx

www.logiquel.com


"Roger Wolter[MSFT]" <rwolter@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:uE3EUVtIHHA.4992@xxxxxxxxxxxxxxxxxxxxxxx
Basically the same problem (plus you need to change the type to 3 to
include element mapping)

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<gpx

xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'

-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT * FROM OPENXML (@idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)

WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')


--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Alain Quesnel" <alainsansspam@xxxxxxxxxxxx> wrote in message
news:%23ji7UnsIHHA.1276@xxxxxxxxxxxxxxxxxxxxxxx
Thank you, that works fine. What if I wanted to add the "ele" field?
When I
try this:

SELECT *
FROM OPENXML (@idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',1)
WITH (lat float, lon float, ele float)

I get this:

lat lon ele
--------------- ----------------- ----------------------
32.02686 -111.57748 NULL
32.02384 -111.577 NULL
32.02074 -111.57645 NULL
32.0176 -111.5759 NULL
32.01447 -111.57535 NULL
(5 row(s) affected)

--

Alain Quesnel
alainsansspam@xxxxxxxxxxxx

www.logiquel.com


"Roger Wolter[MSFT]" <rwolter@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:ON7el6kGHHA.3464@xxxxxxxxxxxxxxxxxxxxxxx
You need to include the namespacein the xpath of OpenXML. The way
you
have it, nothing is found because all the data is contained in the
http://www.topografix.com/GPX/1/1 namespace


DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc =

'<?xml version="1.0"?>

<gpx version="1.1"

creator="GMapToGPX 4.13 - http://www.elsewhere.org/GMapToGPX/";

xmlns="http://www.topografix.com/GPX/1/1";

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";

xsi:schemaLocation="http://www.topografix.com/GPX/1/1

http://www.topografix.com/GPX/1/1/gpx.xsd";>

<rte>

<name>Gmaps Pedometer Route</name>

<cmt>Permalink: <![CDATA[

Permalink temporarily unavailable.

]]>

</cmt>

<rtept lat="32.02686" lon="-111.57748">

<name>Start</name>

<ele>983.8883</ele>

</rtept>

<rtept lat="32.02384" lon="-111.577">

<name>Turn 1</name>

<ele>992.36174</ele>

</rtept>

<rtept lat="32.02074" lon="-111.57645">

<name>Turn 2</name>

<ele>1000.02746</ele>

</rtept>

<rtept lat="32.0176" lon="-111.5759">

<name>Turn 3</name>

<ele>1008.78741</ele>

</rtept>

<rtept lat="32.01447" lon="-111.57535">

<name>Turn 4</name>

<ele>1018.70256</ele>

</rtept>

</rte>

</gpx>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'

-- Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',1)

WITH (lat float,

lon float)


--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Alain Quesnel" <alainsansspam@xxxxxxxxxxxx> wrote in message
news:Ohz0bdbGHHA.5000@xxxxxxxxxxxxxxxxxxxxxxx
I'm trying to open an XML file with OPENXML. I keep getting the
following
error:
The error description is 'The following tags were not closed: gpx,
rte,
rtept.'

This is the full script:


DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =
'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.13 - http://www.elsewhere.org/GMapToGPX/";
xmlns="http://www.topografix.com/GPX/1/1";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd";>
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[
Permalink temporarily unavailable.
]]>
</cmt>
<rtept lat="32.02686" lon="-111.57748">
<name>Start</name>
<ele>983.8883</ele>
</rtept>
<rtept lat="32.02384" lon="-111.577">
<name>Turn 1</name>
<ele>992.36174</ele>
</rtept>
<rtept lat="32.02074" lon="-111.57645">
<name>Turn 2</name>
<ele>1000.02746</ele>
</rtept>
<rtept lat="32.0176" lon="-111.5759">
<name>Turn 3</name>
<ele>1008.78741</ele>
</rtept>
<rtept lat="32.01447" lon="-111.57535">
<name>Turn 4</name>
<ele>1018.70256</ele>
</rtept>
</rte>
</gpx>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/gpx/rte/rtept',1)
WITH (lat float,
lon float)


Thank you,

--

Alain Quesnel
alainsansspam@xxxxxxxxxxxx

www.logiquel.com
















.

Relevant Pages

  • Re: Problem with OPENXML
    ... How can I load the text file to the memory? ... Use of included script samples are subject to the terms specified at ... EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<gpx ... WITH (lat float, lon float, ele float) ...
    (microsoft.public.sqlserver.xml)
  • Re: Problem with OPENXML
    ... OpenRowSet Bulk command can load a file into a blob variable.. ... Use of included script samples are subject to the terms specified at ... EXEC sp_xml_preparedocument @idoc OUTPUT, @doc, '<gpx ... WITH (lat float, lon float, ele float) ...
    (microsoft.public.sqlserver.xml)
  • Re: sound synthesis
    ... > the declarations and using short float helped, ... (defun mix (target-samples source-samples start sample-rate) ... (declare (float sample-rate seconds) ... (defun fm-gong (time freq) ...
    (comp.lang.lisp)
  • Re: Extending T-SQL with COM
    ... Using Excel for this is an extremely heavy weight way of performing what ... declare @rate float ... > GRANT EXECUTE ON dbo.sp_hexadecimal TO Public ...
    (microsoft.public.sqlserver.programming)
  • Re: fgets problem
    ... float f; ... Your array "f" is used only inside main, so there's no reason to ... declare it at file scope. ... can invoke undefined behavior in some cases. ...
    (comp.lang.c)
Quantcast