openxml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

openxml

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-25 : 04:17:07

Hi,
Something very strange seems to be happening in sql server.
Below you can see a sample sql code which you can run in query analyser.
Notice that the sql is retrieving data from the sample xml.

The problem is as follows:

If I run the sample code below, this is the error I get:

"The XML parse error 0xc00ce55f occurred on line number 109, near the XML text " </ma".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'End element was missing the character '>'.'.
Msg 8179, Level 16, State 5, Line 121
Could not find prepared statement with handle 0."

The code runs fine i.e. data is returned if the last element is removed. i.e.
removing <correlation code="COCOA NYBOT:CORN CBOT">9.66923533518683E-02</correlation>

I have identified the problem is not to do with that very last elemnt.
If I remove any elemnt from the xml, then the sql code runs fine and data is returned.

Here is the code:

declare @XMLdoc varchar(8000)

declare @idoc int

set @XMLdoc =
'<marketData>
<date>01-feb-2001 09:53:48</date>
<producedBy>jo johnson</producedBy>
<correlations>
<correlation code="SOYOIL CBOT:SOYOIL CBOT">1</correlation>
<correlation code="SOYOIL CBOT:CORN CBOT">0.446615047947478</correlation>
<correlation code="SOYOIL CBOT:COCOA NYBOT">7.13535194229025E-02</correlation>
<correlation code="SOYOIL CBOT:WTI NYMEX">0.151400710180679</correlation>
<correlation code="SOYOIL CBOT:BR IPE">0.178981117426256</correlation>
<correlation code="SOYOIL CBOT:COTTON NYBOT">0.247842826939373</correlation>
<correlation code="SOYOIL CBOT:COFFEE NYBOT">0.158992146620872</correlation>
<correlation code="SOYOIL CBOT:AL LME">7.64098402497365E-02</correlation>
<correlation code="SOYOIL CBOT:CU LME">9.77841006812836E-02</correlation>
<correlation code="SOYOIL CBOT:PB LME">7.27647952094773E-02</correlation>
<correlation code="SOYOIL CBOT:ZN LME">0.104979980661806</correlation>
<correlation code="SOYOIL CBOT:NG NGA">0.117260577305144</correlation>
<correlation code="SOYOIL CBOT:XAUUSD">0.215943706035572</correlation>
<correlation code="SOYOIL CBOT:XAGUSD">0.141747330784572</correlation>
<correlation code="SOYOIL CBOT:SOYBEAN CBOT">0.72931832277051</correlation>
<correlation code="SOYOIL CBOT:SUGAR NYBOT">0.081240883793423</correlation>
<correlation code="SOYOIL CBOT:SOYMEAL CBOT">0.43084630900272</correlation>
<correlation code="SOYOIL CBOT:WHEAT CBOT">0.269934171223167</correlation>
<correlation code="SOYOIL CBOT:AUDUSD">0.239708583694289</correlation>
<correlation code="SOYOIL CBOT:USDCAD">-0.175923102214138</correlation>
<correlation code="SOYOIL CBOT:USDCHF">-0.213451526459648</correlation>
<correlation code="SOYOIL CBOT:EURUSD">0.211233981442048</correlation>
<correlation code="SOYOIL CBOT:GBPUSD">0.184511473464858</correlation>
<correlation code="SOYOIL CBOT:NZDUSD">0.140633951140378</correlation>
<correlation code="SOYOIL CBOT:USDPLN">-0.097093180976849</correlation>
<correlation code="SOYOIL CBOT:USDSGD">-0.156386835515189</correlation>
<correlation code="SOYOIL CBOT:USDZAR">-1.17687322633844E-02</correlation>
<correlation code="SOYOIL CBOT:F10NWEFB">0</correlation>
<correlation code="SOYOIL CBOT:GO02NWEFB">0</correlation>
<correlation code="SOYOIL CBOT:SOYOIL CBOTeur">1</correlation>
<correlation code="SOYOIL CBOT:CORN CBOTeur">0.446615047947478</correlation>
<correlation code="SOYOIL CBOT:COCOA NYBOTeur">7.13535194229025E-02</correlation>
<correlation code="SOYOIL CBOT:WTI NYMEXeur">0.151400710180679</correlation>
<correlation code="SOYOIL CBOT:BR IPEeur">0.178981117426256</correlation>
<correlation code="SOYOIL CBOT:COTTON NYBOTeur">0.247842826939373</correlation>
<correlation code="SOYOIL CBOT:COFFEE NYBOTeur">0.158992146620872</correlation>
<correlation code="SOYOIL CBOT:AL LMEeur">7.64098402497365E-02</correlation>
<correlation code="SOYOIL CBOT:CU LMEeur">9.77841006812836E-02</correlation>
<correlation code="SOYOIL CBOT:NI LMEeur">5.81316158772319E-02</correlation>
<correlation code="SOYOIL CBOT:PB LMEeur">7.27647952094773E-02</correlation>
<correlation code="SOYOIL CBOT:ZN LMEeur">0.104979980661806</correlation>
<correlation code="SOYOIL CBOT:NG NGAeur">0.117260577305144</correlation>
<correlation code="SOYOIL CBOT:XAUUSDeur">0.215943706035572</correlation>
<correlation code="SOYOIL CBOT:XAGUSDeur">0.141747330784572</correlation>
<correlation code="SOYOIL CBOT:SOYBEAN CBOTeur">0.72931832277051</correlation>
<correlation code="SOYOIL CBOT:SUGAR NYBOTeur">0.081240883793423</correlation>
<correlation code="SOYOIL CBOT:SOYMEAL CBOTeur">0.43084630900272</correlation>
<correlation code="SOYOIL CBOT:WHEAT CBOTeur">0.269934171223167</correlation>
<correlation code="SOYOIL CBOT:F10NWEFBeur">0</correlation>
<correlation code="SOYOIL CBOT:GO02NWEFBeur">0</correlation>
<correlation code="CORN CBOT:SOYOIL CBOT">0.446615047947478</correlation>
<correlation code="CORN CBOT:CORN CBOT">1</correlation>
<correlation code="CORN CBOT:COCOA NYBOT">9.66923533518683E-02</correlation>
<correlation code="CORN CBOT:WTI NYMEX">2.95638851407328E-02</correlation>
<correlation code="CORN CBOT:BR IPE">2.74783930826539E-02</correlation>
<correlation code="CORN CBOT:COTTON NYBOT">0.156175715076018</correlation>
<correlation code="CORN CBOT:COFFEE NYBOT">0.21563840287908</correlation>
<correlation code="CORN CBOT:AL LME">0.107696109461046</correlation>
<correlation code="CORN CBOT:CU LME">0.104488612215538</correlation>
<correlation code="CORN CBOT:NI LME">6.60725784971671E-02</correlation>
<correlation code="CORN CBOT:PB LME">7.34143670717396E-02</correlation>
<correlation code="CORN CBOT:ZN LME">0.179568571685891</correlation>
<correlation code="CORN CBOT:NG NGA">6.33318052886849E-02</correlation>
<correlation code="CORN CBOT:XAUUSD">0.220103381106155</correlation>
<correlation code="CORN CBOT:XAGUSD">0.220979558311397</correlation>
<correlation code="CORN CBOT:SOYBEAN CBOT">0.503095556862196</correlation>
<correlation code="CORN CBOT:SUGAR NYBOT">0.1098792207624</correlation>
<correlation code="CORN CBOT:SOYMEAL CBOT">0.424612765820417</correlation>
<correlation code="CORN CBOT:WHEAT CBOT">0.583300088481644</correlation>
<correlation code="CORN CBOT:AUDUSD">0.132083148922414</correlation>
<correlation code="CORN CBOT:USDCAD">-0.066595806688049</correlation>
<correlation code="CORN CBOT:USDCHF">-9.74468693234856E-02</correlation>
<correlation code="CORN CBOT:EURUSD">0.103847036094941</correlation>
<correlation code="CORN CBOT:GBPUSD">0.158799468414616</correlation>
<correlation code="CORN CBOT:NZDUSD">9.86151512349353E-02</correlation>
<correlation code="CORN CBOT:USDPLN">-3.63738747465425E-02</correlation>
<correlation code="CORN CBOT:USDSGD">-0.115459673408455</correlation>
<correlation code="CORN CBOT:USDZAR">2.24750166616744E-02</correlation>
<correlation code="CORN CBOT:F10NWEFB">0</correlation>
<correlation code="CORN CBOT:GO02NWEFB">0</correlation>
<correlation code="CORN CBOT:SOYOIL CBOTeur">0.446615047947478</correlation>
<correlation code="CORN CBOT:CORN CBOTeur">1</correlation>
<correlation code="CORN CBOT:COCOA NYBOTeur">9.66923533518683E-02</correlation>
<correlation code="CORN CBOT:WTI NYMEXeur">2.95638851407328E-02</correlation>
<correlation code="CORN CBOT:BR IPEeur">2.74783930826539E-02</correlation>
<correlation code="CORN CBOT:COTTON NYBOTeur">0.156175715076018</correlation>
<correlation code="CORN CBOT:COFFEE NYBOTeur">0.21563840287908</correlation>
<correlation code="CORN CBOT:AL LMEeur">0.107696109461046</correlation>
<correlation code="CORN CBOT:CU LMEeur">0.104488612215538</correlation>
<correlation code="CORN CBOT:NI LMEeur">6.60725784971671E-02</correlation>
<correlation code="CORN CBOT:PB LMEeur">7.34143670717396E-02</correlation>
<correlation code="CORN CBOT:ZN LMEeur">0.179568571685891</correlation>
<correlation code="CORN CBOT:NG NGAeur">6.33318052886849E-02</correlation>
<correlation code="CORN CBOT:XAUUSDeur">0.220103381106155</correlation>
<correlation code="CORN CBOT:XAGUSDeur">0.220979558311397</correlation>
<correlation code="CORN CBOT:SOYBEAN CBOTeur">0.503095556862196</correlation>
<correlation code="CORN CBOT:SUGAR NYBOTeur">0.1098792207624</correlation>
<correlation code="CORN CBOT:SOYMEAL CBOTeur">0.424612765820417</correlation>
<correlation code="CORN CBOT:WHEAT CBOTeur">0.583300088481644</correlation>
<correlation code="CORN CBOT:F10NWEFBeur">0</correlation>
<correlation code="CORN CBOT:GO02NWEFBeur">0</correlation>
<correlation code="COCOA NYBOT:SOYOIL CBOT">7.13535194229025E-02</correlation>
<correlation code="COCOA NYBOT:CORN CBOT">9.66923533518683E-02</correlation>
</correlations>
</marketData>'

begin

EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLdoc

select
code,
convert(float, correlation)
FROM OPENXML (@idoc, '/marketData/correlations/correlation',1)
WITH (
code varchar(50),
correlation varchar(50) 'text()'
)

end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 04:22:23

Given code works absolutely fine on my box!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-25 : 04:33:44

try this please.

declare @XMLdoc varchar(8000)

declare @idoc int

set @XMLdoc =
'<marketData>
<date>01-jan-2007 09:53:48</date>
<producedBy>o johnson</producedBy>
<correlations>
<correlation code="SOYOIL CBOT:SOYOIL CBOT">1</correlation>
<correlation code="SOYOIL CBOT:CORN CBOT">0.446615047947478</correlation>
<correlation code="SOYOIL CBOT:COCOA NYBOT">7.13535194229025E-02</correlation>
<correlation code="SOYOIL CBOT:WTI NYMEX">0.151400710180679</correlation>
<correlation code="SOYOIL CBOT:BR IPE">0.178981117426256</correlation>
<correlation code="SOYOIL CBOT:COTTON NYBOT">0.247842826939373</correlation>
<correlation code="SOYOIL CBOT:COFFEE NYBOT">0.158992146620872</correlation>
<correlation code="SOYOIL CBOT:AL LME">7.64098402497365E-02</correlation>
<correlation code="SOYOIL CBOT:CU LME">9.77841006812836E-02</correlation>
<correlation code="SOYOIL CBOT:PB LME">7.27647952094773E-02</correlation>
<correlation code="SOYOIL CBOT:ZN LME">0.104979980661806</correlation>
<correlation code="SOYOIL CBOT:NG NGA">0.117260577305144</correlation>
<correlation code="SOYOIL CBOT:XAUUSD">0.215943706035572</correlation>
<correlation code="SOYOIL CBOT:XAGUSD">0.141747330784572</correlation>
<correlation code="SOYOIL CBOT:SOYBEAN CBOT">0.72931832277051</correlation>
<correlation code="SOYOIL CBOT:SUGAR NYBOT">0.081240883793423</correlation>
<correlation code="SOYOIL CBOT:SOYMEAL CBOT">0.43084630900272</correlation>
<correlation code="SOYOIL CBOT:WHEAT CBOT">0.269934171223167</correlation>
<correlation code="SOYOIL CBOT:AUDUSD">0.239708583694289</correlation>
<correlation code="SOYOIL CBOT:USDCAD">-0.175923102214138</correlation>
<correlation code="SOYOIL CBOT:USDCHF">-0.213451526459648</correlation>
<correlation code="SOYOIL CBOT:EURUSD">0.211233981442048</correlation>
<correlation code="SOYOIL CBOT:GBPUSD">0.184511473464858</correlation>
<correlation code="SOYOIL CBOT:NZDUSD">0.140633951140378</correlation>
<correlation code="SOYOIL CBOT:USDPLN">-0.097093180976849</correlation>
<correlation code="SOYOIL CBOT:USDSGD">-0.156386835515189</correlation>
<correlation code="SOYOIL CBOT:USDZAR">-1.17687322633844E-02</correlation>
<correlation code="SOYOIL CBOT:F10NWEFB">0</correlation>
<correlation code="SOYOIL CBOT:GO02NWEFB">0</correlation>
<correlation code="SOYOIL CBOT:SOYOIL CBOTeur">1</correlation>
<correlation code="SOYOIL CBOT:CORN CBOTeur">0.446615047947478</correlation>
<correlation code="SOYOIL CBOT:COCOA NYBOTeur">7.13535194229025E-02</correlation>
<correlation code="SOYOIL CBOT:WTI NYMEXeur">0.151400710180679</correlation>
<correlation code="SOYOIL CBOT:BR IPEeur">0.178981117426256</correlation>
<correlation code="SOYOIL CBOT:COTTON NYBOTeur">0.247842826939373</correlation>
<correlation code="SOYOIL CBOT:COFFEE NYBOTeur">0.158992146620872</correlation>
<correlation code="SOYOIL CBOT:AL LMEeur">7.64098402497365E-02</correlation>
<correlation code="SOYOIL CBOT:CU LMEeur">9.77841006812836E-02</correlation>
<correlation code="SOYOIL CBOT:NI LMEeur">5.81316158772319E-02</correlation>
<correlation code="SOYOIL CBOT:PB LMEeur">7.27647952094773E-02</correlation>
<correlation code="SOYOIL CBOT:ZN LMEeur">0.104979980661806</correlation>
<correlation code="SOYOIL CBOT:NG NGAeur">0.117260577305144</correlation>
<correlation code="SOYOIL CBOT:XAUUSDeur">0.215943706035572</correlation>
<correlation code="SOYOIL CBOT:XAGUSDeur">0.141747330784572</correlation>
<correlation code="SOYOIL CBOT:SOYBEAN CBOTeur">0.72931832277051</correlation>
<correlation code="SOYOIL CBOT:SUGAR NYBOTeur">0.081240883793423</correlation>
<correlation code="SOYOIL CBOT:SOYMEAL CBOTeur">0.43084630900272</correlation>
<correlation code="SOYOIL CBOT:WHEAT CBOTeur">0.269934171223167</correlation>
<correlation code="SOYOIL CBOT:F10NWEFBeur">0</correlation>
<correlation code="SOYOIL CBOT:GO02NWEFBeur">0</correlation>
<correlation code="CORN CBOT:SOYOIL CBOT">0.446615047947478</correlation>
<correlation code="CORN CBOT:CORN CBOT">1</correlation>
<correlation code="CORN CBOT:COCOA NYBOT">9.66923533518683E-02</correlation>
<correlation code="CORN CBOT:WTI NYMEX">2.95638851407328E-02</correlation>
<correlation code="CORN CBOT:BR IPE">2.74783930826539E-02</correlation>
<correlation code="CORN CBOT:COTTON NYBOT">0.156175715076018</correlation>
<correlation code="CORN CBOT:COFFEE NYBOT">0.21563840287908</correlation>
<correlation code="CORN CBOT:AL LME">0.107696109461046</correlation>
<correlation code="CORN CBOT:CU LME">0.104488612215538</correlation>
<correlation code="CORN CBOT:NI LME">6.60725784971671E-02</correlation>
<correlation code="CORN CBOT:PB LME">7.34143670717396E-02</correlation>
<correlation code="CORN CBOT:ZN LME">0.179568571685891</correlation>
<correlation code="CORN CBOT:NG NGA">6.33318052886849E-02</correlation>
<correlation code="CORN CBOT:XAUUSD">0.220103381106155</correlation>
<correlation code="CORN CBOT:XAGUSD">0.220979558311397</correlation>
<correlation code="CORN CBOT:SOYBEAN CBOT">0.503095556862196</correlation>
<correlation code="CORN CBOT:SUGAR NYBOT">0.1098792207624</correlation>
<correlation code="CORN CBOT:SOYMEAL CBOT">0.424612765820417</correlation>
<correlation code="CORN CBOT:WHEAT CBOT">0.583300088481644</correlation>
<correlation code="CORN CBOT:AUDUSD">0.132083148922414</correlation>
<correlation code="CORN CBOT:USDCAD">-0.066595806688049</correlation>
<correlation code="CORN CBOT:USDCHF">-9.74468693234856E-02</correlation>
<correlation code="CORN CBOT:EURUSD">0.103847036094941</correlation>
<correlation code="CORN CBOT:GBPUSD">0.158799468414616</correlation>
<correlation code="CORN CBOT:NZDUSD">9.86151512349353E-02</correlation>
<correlation code="CORN CBOT:USDPLN">-3.63738747465425E-02</correlation>
<correlation code="CORN CBOT:USDSGD">-0.115459673408455</correlation>
<correlation code="CORN CBOT:USDZAR">2.24750166616744E-02</correlation>
<correlation code="CORN CBOT:F10NWEFB">0</correlation>
<correlation code="CORN CBOT:GO02NWEFB">0</correlation>
<correlation code="CORN CBOT:SOYOIL CBOTeur">0.446615047947478</correlation>
<correlation code="CORN CBOT:CORN CBOTeur">1</correlation>
<correlation code="CORN CBOT:COCOA NYBOTeur">9.66923533518683E-02</correlation>
<correlation code="CORN CBOT:WTI NYMEXeur">2.95638851407328E-02</correlation>
<correlation code="CORN CBOT:BR IPEeur">2.74783930826539E-02</correlation>
<correlation code="CORN CBOT:COTTON NYBOTeur">0.156175715076018</correlation>
<correlation code="CORN CBOT:COFFEE NYBOTeur">0.21563840287908</correlation>
<correlation code="CORN CBOT:AL LMEeur">0.107696109461046</correlation>
<correlation code="CORN CBOT:CU LMEeur">0.104488612215538</correlation>
<correlation code="CORN CBOT:NI LMEeur">6.60725784971671E-02</correlation>
<correlation code="CORN CBOT:PB LMEeur">7.34143670717396E-02</correlation>
<correlation code="CORN CBOT:ZN LMEeur">0.179568571685891</correlation>
<correlation code="CORN CBOT:NG NGAeur">6.33318052886849E-02</correlation>
<correlation code="CORN CBOT:XAUUSDeur">0.220103381106155</correlation>
<correlation code="CORN CBOT:XAGUSDeur">0.220979558311397</correlation>
<correlation code="CORN CBOT:SOYBEAN CBOTeur">0.503095556862196</correlation>
<correlation code="CORN CBOT:SUGAR NYBOTeur">0.1098792207624</correlation>
<correlation code="CORN CBOT:SOYMEAL CBOTeur">0.424612765820417</correlation>
<correlation code="CORN CBOT:WHEAT CBOTeur">0.583300088481644</correlation>
<correlation code="CORN CBOT:F10NWEFBeur">0</correlation>
<correlation code="CORN CBOT:GO02NWEFBeur">0</correlation>
<correlation code="COCOA NYBOT:SOYOIL CBOT">7.13535194229025E-02</correlation>
<correlation code="COCOA NYBOT:CORN CBOT">9.66923533518683E-02</correlation>
</correlations>
</marketData>'

begin

EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLdoc

select
code,
convert(float, correlation)
FROM OPENXML (@idoc, '/marketData/correlations/correlation',1)
WITH (
code varchar(50),
correlation varchar(50) 'text()'
)

end

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2007-06-25 : 04:36:37

It appears that if I copy and paste the code that does not work into this site, and then copy from this sitre and paste into query analyser, then the code runs fine.
Any ideas please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources