tag:blogger.com,1999:blog-497787815177352569.post4656984422690565208..comments2024-01-03T08:55:04.827-05:00Comments on SQL Anywhere: OpenXML() Rocks!Breck Carterhttp://www.blogger.com/profile/15975598564711761434noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-497787815177352569.post-11692851381831990552015-06-16T14:10:35.629-04:002015-06-16T14:10:35.629-04:00Hi,
I have a simple XML. When I parse that xml w...Hi,<br /><br />I have a simple XML. When I parse that xml with OPENXML , I am getting NULL values.Can some one help me if I'm doing wrong.<br /><br />XML<br />====================================<br /><br /><br /> <br /> Rikke <br /><br /> Norberg <br /><br /> <br /> xxx@gmail.com <br /><br /> <br /> <br /> <br /> <br /> User <br /><br /> <br /> <br /> <br /> User_2 <br /><br /> <br /> <br /> <br /> <br /> <br /><br /><br /> Stored Procedure for Parsing<br />====================================<br />create procedure uspparsexml_2<br /><br />(<br /><br />@strXML VARCHAR(MAX)<br /><br />)<br /><br />AS<br /><br />BEGIN<br /><br />SET NOCOUNT ON<br /><br /><br /><br />DECLARE @docHandle INT<br /><br />EXEC sp_xml_preparedocument @docHandle OUTPUT, @strXML<br /><br />declare @FirstName varchar(50)<br /><br />SELECT @FirstName=FirstName<br /><br /><br /><br /> FROM OPENXML (@docHandle, 'UserRole/FirstName') <br /><br /><br /><br /> WITH (FirstName VARCHAR(50) 'FirstName')<br /><br /> print @FirstName<br /><br /> select @FirstName<br /><br /> EXEC sp_xml_removedocument @docHandle<br /><br /> print 'last'<br /><br /> print @FirstName<br /><br /> select @FirstName<br /><br /> print 'last'<br /><br /><br /><br />END<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-52284631185845242672009-11-30T07:05:37.017-05:002009-11-30T07:05:37.017-05:00@Gio: Ooops, you did say SA10, didn't you? Pre...@Gio: Ooops, you did say SA10, didn't you? Pretty much the same, this stuff's been around for a long time, but I'm not sure if the docs are available online at dcx.sybase.com, you may have to use the compiled HTML Help that comes with the download.Breck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-86999669666823091942009-11-30T07:01:33.444-05:002009-11-30T07:01:33.444-05:00@Gio: There's Interactive SQL OUTPUT statement...@Gio: There's Interactive SQL OUTPUT statement and the ADO.NET DataSet object, then there's UNLOAD SELECT ... FOR XML AUTO, FOR XML EXPLICIT, FOR XML RAW, XMLAGG, XMLCONCAT, XMLELEMENT, XMLFOREST, XMLGEN, not to mention XML web services... check out the SQL Anywhere 11 Help "Using XML in the database" dcx.sybase.com/1101en/dbusage_en11/ug-sqlxml.html (which seems to be down at this exact moment)Breck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-18721223306348995072009-11-27T09:58:00.775-05:002009-11-27T09:58:00.775-05:00Is there any easy way to do the oposite operation,...Is there any easy way to do the oposite operation, i.e. dump data from a SA10 table as XML?Giohttps://www.blogger.com/profile/00933929550278471245noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-30156225841535767492008-07-04T23:06:00.000-04:002008-07-04T23:06:00.000-04:00Apologies for referring you as Brent instead of Br...Apologies for referring you as Brent instead of Breck in the previous post and also on the newsgroup message.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-32670702588260323412008-07-04T01:39:00.000-04:002008-07-04T01:39:00.000-04:00Hi Brent,I recently posted more details about this...Hi Brent,<BR/>I recently posted more details about this problem on the Sybase forum, in reply to your post there. Please see if it gives you more idea on the same...<BR/><BR/>Here is the link:<BR/><BR/>http://groups.google.com/group/<BR/>sybase.public.sqlanywhere.general/<BR/>browse_thread/thread/<BR/>cc231fbbff7b4689/7c57c09e935ee9c1Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-86149449807159153042008-06-19T18:25:00.000-04:002008-06-19T18:25:00.000-04:00Here's the full link...http://forums.sybase.com/cg...Here's the full link...<BR/><BR/>http://forums.sybase.com/cgi-bin/webnews.cgi<BR/>?cmd=item-120839&group=sybase<BR/>%2Epublic%2Esqlanywhere%2EgeneralBreck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-53732269343831757782008-06-19T13:50:00.000-04:002008-06-19T13:50:00.000-04:00Here's a link to an article on the newsgroup that ...Here's a link to an article on the newsgroup that explains this issue:<BR/><BR/>http://forums.sybase.com/cgi-bin/webnews.cgi?cmd=item-120839&group=sybase%2Epublic%2Esqlanywhere%2Egeneral<BR/><BR/>Abhishek, I'm willing to give detailed help with finding a workaround if you wish.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-23729714367192782912008-06-14T05:48:00.000-04:002008-06-14T05:48:00.000-04:00You should post a question like this on the genera...You should post a question like this on the general SQL Anywhere newsgroup; see the "how to" at http://www.sybase.com<BR/>/detail?id=1002557<BR/><BR/>(hmmmm, I'm starting to dislike blogger... :)<BR/><BR/>Anyway, I have not seen that error. The description suggests you increase the database cache, and if you are using a busy server with lots of connections your usage of the RAM cache may be limited. Send me an email with more details, and we'll just post the solution here:<BR/>breck dot carter at gmail dot com<BR/><BR/>Insufficient memory for OPENXML query <BR/>SQLCODE –912 <BR/><BR/>The memory used by an OPENXML query may be proportional to the size of the largest subtree in the XML document that has nodes referred to by a single row in the result. Try increasing the cache size or rewriting the query so that rows refer to smaller subtrees.Breck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-60476485964042478822008-06-12T16:08:00.000-04:002008-06-12T16:08:00.000-04:00The link I mentioned got messed up in the comment....The link I mentioned got messed up in the comment. Here is what I looked at: <BR/>http://www.ianywhere.com/developer/<BR/>product_manuals/sqlanywhere/0901/en/<BR/>html/dberen9/00000273.htmAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-40441103027314236512008-06-12T16:05:00.000-04:002008-06-12T16:05:00.000-04:00Hi Breck,I am having a trouble with OpenXML() and ...Hi Breck,<BR/>I am having a trouble with OpenXML() and thought of discussing with you. I am trying to insert a big (but simple) XML into a single Sybase table... there are 37804 records which will map to as many rows in the target table. But when I execute this query, it returns after a couple of minutes only to display an error "Insufficient memory for OPENXML query (-912)" :-(<BR/><BR/>I looked at the error description at http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dberen9/00000273.htm but not to much avail.<BR/><BR/>Can you provide some pointers? From your original post, it looked like OpenXML() will be able to handle large amount of data without any problems but looks like it is not.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-72318396032060762742008-05-26T15:43:00.000-04:002008-05-26T15:43:00.000-04:00Your wish is my command! :)http://sqlanywhere.blog...Your wish is my command! :)<BR/><BR/>http://sqlanywhere.blogspot.com/2008/05/executing-begin-blocks.htmlBreck Carterhttps://www.blogger.com/profile/15975598564711761434noreply@blogger.comtag:blogger.com,1999:blog-497787815177352569.post-40692918138343321662008-05-26T12:02:00.000-04:002008-05-26T12:02:00.000-04:00OpenXML() rocks and you rock too!!!Thanks very muc...OpenXML() rocks and you rock too!!!<BR/>Thanks very much for responding to my query at sybase.public.sqlanywhere (Ref: Pushing data from XML to Sybase tables). I read your comment and your entire blog post also. It is so cool. You have given so much details at every level, it can not get better. It works and I really liked it (more so because I am new to this area). <BR/><BR/>However I still have not figured out if it _has_to_be a procedure or can it be a sql query also. I am using Interactive SQL (SQL Anywhere 10) and it requires stuff like BEGIN, END etc. Please reply (at your blog) if there is a way it can be done in a SQL statement also.<BR/><BR/>Thanks very much once again.<BR/>Keep writing good stuff.<BR/><BR/>~ Abhishek<BR/><BR/>PS: My real name is Abhishek. Somehow the NNTP client I used to post the previous message messed up my name and email id and showed some one else's. :-)Anonymousnoreply@blogger.com