sqlxml

Convert C# 2.0 System.Data.SqlTypes.SqlXml object into a System.Xml.XmlNode

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 ...

TSQL: How do I do a self-join in XML to get a nested document?

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: ...

SQL Pivot using an XML column

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...

SQL Server For Xml Path

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...

SQLXML with Windows 2008 and SQL Server 2008

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...

MS SQL 2005 "For XML Path" Node Layout Question

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<...

How can I use sp_xml_preparedocument on result of NTEXT query in SQL 2000?

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 ...

Why would a schema make an XML column slower?

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...

XML xs:choice SQLXMLBulkload

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...

Accessing XML stream from ADO against SQL Server 2008.

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...

How do I overcome OpenXML's 8000 character limit?

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 select tags from an SQL XML Query?

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> ...

SQL Server: Output an XML field as tabular data using a stored procedure

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</...

SQL - Formatting XML response value

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...

Why is SQL 2005 calling CONVERT when passed a sqlxml parameter?

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, ...

SQLXML in SQL server

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"&gt; <move> <player>white</player> <piece>pawn</piece> <start>A2</start> <end>A3</end> </move> <move...

rank over shredded xml

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...

SQL server insert data from table into an XML variable

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...

Tool to create SQLXMLBulkLoad Schemas?

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? ...

Looking for an efficient way to pass a collection parameter to a SQL Stored Procedure

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...