I seem to always have problems with converting data to and from XML in C#. It always wants you to create a full XMLDocument object even when you think you shouldn't have to. In this case I have a SQLXML column in a MS SQL 2005 server that I am trying to pull out and push into a function that requires an XMLNode as a parameter. You would ...
I have a SQL Server 2005 table like this: create table Taxonomy( CategoryId integer primary key, ParentCategoryId integer references Taxonomy(CategoryId), CategoryDescription varchar(50) ) with data looking like CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar I'd like to query it into an xml document like this: ...
Does anyone have an example of a pivot using a table with an XML column in it - specifically turning some of the elements in the xml column into a column in the pivot table? I'm trying to build one, but the syntax is killing me; xml in sql is damn hard to work with, compared to xml in C#. I suspect this is impossible, but it wouldn't hur...
I am returning an xml result for a query. Given two tables: **Foo** FooId int FooName varchar(10) **Bar** BarId int FooId int (FK) BarName varchar(10) I create a select statement like: SELECT FooId, FooName, ( SELECT BarId, FooId, BarName FROM Bar WHERE Bar.FooId = Foo.FooId AND...
Hi all, I have an application that uses SQLXML to access data on the database. We have it working on a Windows 2003 server and SQL Server 2005. Now the client wants to install it on Windows 2008 and SQL Server 2008 and we are getting errors like: Microsoft.Data.SqlXml.SqlXmlException: Class not registered ---> System.Runtime.InteropServ...
I have the following query Select field1 as 'node1/field1', field2 as 'node1/field2', (Select field3 as 'child1/field3', field4 as 'child1/field4' From table2 FOR XML PATH(''),TYPE,Elements) From Table1 FOR XML PATH('Root'),Elements This produces: <Root> <node1> <field1>data1</field1> <field2>data2<...
I know NTEXT is going away and that there are larger best-practices issues here (like storing XML in an NTEXT column), but I have a table containing XML from which I need to pluck a attribute value. This should be easy to do using sp_xml_preparedocument but is made more tricky by the fact that you cannot declare a local variable of type ...
I have a table with an XML column. The documents are pretty large and numerous, and I've been trying various ways to improve the performance of basic queries. Because the documentation indicates that applying an XML Schema to the column can help SQL Server to optimize queries — and because one was available — I created a schema collect...
I have some XML files that I need to batch process into SQL Server. The following Schema and XML sections outline an area I'm having trouble with. <xs:complexType> <xs:sequence> <xs:choice maxOccurs="unbounded"> <xs:element name="TextLine" type="xs:string" sql:field="AdvertLine" sql:relation="XmlAdvert" sql:rela...
We are migrating ASP code that used ADO to connect to SQL Server 2000. For the most part the code migrated without a hitch to SQL Server 2008 after defining the connection, but one type of query is throwing an error that puzzles me. Against SQL 2000, we would use code like this: set oCommand = Server.CreateObject("ADODB.Command") oComm...
I'm loading an XML in SQL using OpenXML while declaring the variable the max i can go up to is 8000 chars : DECLARE @xml_text varchar(8000) Since text, ntext is not allowed to be used with openXML what other alternatives do i have to load the entire XML (over 20000 chars) in SQL ? ...
How can I retrieve the fields within an XML field in MS SQL? Every query I try does not work as intended whenever I use this XML code: <soap:Envelope xmlns:xsi="[URI]" xmlns:xsd="[URI]" xmlns:soap="[URI]"> <soap:Body> <RunPackage xmlns="[URI]"> <xmlDoc> <Request> <SubscriberCode>76547654</SubscriberCode> ...
I am using a table with an XML data field to store the audit trails of all other tables in the database. That means the same XML field has various XML information. For example my table has two records with XML data like this: 1st record: <client> <name>xyz</name> <ssn>432-54-4231</ssn> </client> 2nd record: <emp> <name>abc</...
Hi I want to receive my response XML in following format... <PersonDetails> <Name>Ajay</Name> <Age>29</Age> <ContactDetails> <ContactDetail> <ContactType>Mobile</ContactType> <ContactValue>9565649556</ContactValue> </ContactDetail> <ContactDetail> <ContactType>Email</ContactType> <Cont...
Hi I am declaring an SqlXml param and passing it a XmlReader. This then gets parametrized and the SQL gets executed all very well. However, when looking at the SQL which gets executed through profiler I notice that it is taking the SqlXml parameter and calling CONVERT on it... CONVERT (xml, ) Why does the server need to call CONVERT, ...
I'm having an small issue with SQLXML in SQL Server 2008. Here's my testing SQL query DECLARE @XMLTable TABLE(GameHistory XML) INSERT INTO @XMLTable VALUES( '<game xmlns="http://my.name.space"> <move> <player>white</player> <piece>pawn</piece> <start>A2</start> <end>A3</end> </move> <move...
Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case). Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with usin...
How can I insert a whole bunch of rows into an XML variable without using a cursor? I know I can do SET @errors.modify('insert <error>{ sql:variable("@text") }</error> as last into /errors[1]') to insert the value of a variable, but I want to basically do SET @errors.modify(SELECT 'insert <error>{ sql:column("text") }</error>' FROM t...
SQLXMLBulkLoad requires schemas to be annotated with various attributes (e.g. sql:datatype) and some additional elements. Is there any tool that can enrich a schema with this information for at least some basic scenarios? ...
Hi, I need to pass a collection of objects as a parameter to a stored procedure. Initially, I thought of serializing this collection and passing it to the procedure as a xml string. Then, inside the procedure, I'd use XML to get the data from the string parameter and use it. But I thought this approach was not performatic at all. I wa...