for-xml

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

Architecture Critique: SQL Server FOR XML into XSLT for Rich HTML Interface

Can I get some constructive feedback about the following architecture? Simplified Architecture Summary: Return XML from your SQL Server (using FOR XML) and pass it straight into a XSL transform to produce a rich HTML web site. What are the pro’s and con’s of such a system when compared with a conventional 3-tier ASP.NET architecture?...

MS TSQL for xml path question regarding xpath element

Hi there, I have the following TSQL statement: select tblName "TblName", structure "TblName/STRUCTURE", sqlRetrieve "TblName/SQLRETRIEVE", Identifier "TblName/IDENTIFIER", '2' "TblName/OBJECTTYPE" from configTable for xml path ('') which outputs: <TblName>PD_CODE_PRODUCTS <STRUCTURE>PD_CODE_PRODUCTS</...

.NET XmlReader with 'FOR XML' data

Recently, in company we got some MSSQL database from some old project which we have to integrate in current solution. Database has about 100-150 stored procedures that use FOR XML AUTO clause, so that queries return complete object-graph as XML instead of rows. Quickest solution (for us in company) was to create serializable classes (w...

SQL Server FOR XML adding in a tag

Heya, So I am pulling data from a SQL Server 2000 DB then converting it to XML using FOR XML AUTO. The XML I get back looks like this. <Order OrderNumber="2000004" DeliveryPickupCharge="5.9900" SalesTaxTotal="0.0000" SubTotal="0.0000" Total="5.9900"> <Customer FirstName="Anthony" LastName="Caporale"> <Product ProductName="Pap...

SQL Server 2005 "FOR XML PATH" Grouping

I'm trying to generate a XML document from the SQL Server 2005 database by using "FOR XML" construct. There are two simple tables in the database with a one-to-many relationship: 1) Magazines | Id | Number | Name | ---------------------------- | 53 | 0001 | Magazine 1 | | 54 | 0002 | Magazine 2 | | 55 | 0003 | Magazi...

Writing the results of a FOR XML query to a file with C#.NET

Hello, I'm trying to write the result of a FOR XML PATH query to a file. I can generate the file, but it doesn't contain the results of the query. Any one know where i'm going wrong? private static void GetChartData(string OC_Ttl1, string OC_Ttl2, string OC_OL31) { //Prepare Connection Variables SqlConnection conn_...

Create XML from SQL Server 2005 data using FOR XML

I'm trying to create an Excel XML that I want to store in an XML Field in SQL Server 2005. I have gotten this far: WITH XMLNAMESPACES ( 'urn:schemas-microsoft-com:office:spreadsheet' as "s", 'urn:schemas-microsoft-com:office:office' as "o", 'urn:schemas-microsoft-com:office:excel' as "x" ) select 'Order' as "@s:Name", ( selec...

Using "FOR XML" with XML Serialization with DATETIME type

I'm having issues trying to add some dates to a pre-existing class that is loaded via XML Serialisation, and it's not doing what I thought it should do. I knocked up a basic test with SQL of (where EffectiveFrom and EffectiveTo are declared as DATETIME) SELECT o.EffectiveFrom AS [@EffectiveFrom], o.EffectiveTo AS [@EffectiveTo], FROM...

Truly empty element with sql server for xml-directive

How do I create a truly empty xml element with the for xml-directive in sql server (2005)? Example: select '' for xml path('element'), root('elements') Outputs: <elements><element></element></elements> But what I really want is: <elements><element /></elements> ...

Writing XML to memory instead of disk

How do i go about writing the results of a FOR XML PATH stored procedure into memory rather than a file on disk? Current way of doing things: private void GetChartData(string OC_Ttl1, string OC_Ttl2, string OC_OL31) { OC_Ttl_1 = OC_Ttl1; OC_Ttl_2 = OC_Ttl2; OC_OL3_1 = OC_OL31; //Output xml DataSet orgDataSet = new ...

Not able to get desired output while generating xml files from SQL query from SQL Server

I am executing this query select category "ROOT/category", question "Category/question", option1 "Category/option1" from testDB2 for XML PATH ('ROOT') , ELEMENTS Presently the database has three entries and the xml file i get is this <ROOT> <ROOT> <category>maths</category> </ROOT> <Category> <question>2+2?</question>...

How to return SQL Server 2005/2008 columns as identical child nodes using FOR XML query?

Basically I need to return some data from a SQL Server table in the following XML format: <querydata> <entity name="Person.Contact"> <row> <field name="FirstName">Gustavo</field> <field name="LastName">Achong</field> </row> <row> <field name="FirstName">Catherine</field> <field name="LastName">Abel<...

SQL FOR XML Help

Prob an easy question, but I am new to forming XML in SQL 2005, but What would be the best FOR XML SQL statement to use to form the XML seen below from a table that looks like this? Column1 Column2 ------------------------ Baseball Football Cricket Polo Swim Beach Desired XML output: <Category Nam...

Selecting Nested Xml Elements using FOR XML - Best Practice

I am working on retrieving data in Xml format from SQL Server 05 using FOR XML What is the best practice for nesting elements in my resulting Xml? Currently I am doing this: Select ( Select [Col1] As [Col1], [Col2] As [Col2] From [dbo].[NestedTable] As T1 Where T0.[Key] = T1.[Key] ...

How do I suppress empty namespaces with FOR XML in Sql Server

We are encountering a strange problem with SQL Server 2005/2008 using the FOR XML with fragments of xml and namespaces. Here is the query in question. WITH XMLNAMESPACES ( DEFAULT 'http://tempuri.org/newincomingxml.xsd', 'http://tempuri.org/newincomingxml.xsd' as [xsi], 'http://tempuri.org/newincomingxml.xsd' as [a] ) SELECT [@a:Sou...

sql server 2000 and for xml explicit

Hi everyone, I've got a problem with using for xml explicit in SQL Server 2000 (so I can't use the new path() stuff from sql 2005/8) Essentially I have two tables and the XML structure I want to have is <xml> <table_1 field1="foo" field2="foobar2" field3="foobar3"> <a_row_from_table_2 field1="goo" field2="goobar2" field3="gooba...

Row concat from this query

I have this query: SELECT DISTINCT IM.EDIFICIOS_ID, TI.TITULAR FROM IMPORTACION IM INNER JOIN I_EDIFICIO IE ON IM.IMPORTACION_ID=IE.IMPORTACION_ID INNER JOIN I_EDIFICIO_TITULAR ET ON IM.IMPORTACION_ID=ET.IMPORTACION_ID AND IE.EDIFICIO_ID=ET.EDIFICIO_ID INNER JOIN I_TITULAR TI ON IM.IMPORTACION_ID=TI.IMPORTACION_ID AND ET.TITULAR_ID=TI.T...

Using FOR XML AUTO against a synonym

We've just switched to synonyms for linked server stuff, and noticed that our FOR XML output is no longer correct. When returning XML results from a view, we could alias the view and that would be assigned as the element name. With synonyms, however, it seems to ignore the alias? We're still mostly on SQL 2005 - this bug doesn't seem ...

Entity framework calling a FOR XML stored procedure truncates at 2033 characters

I have a stored procedure which uses a FOR XML statement at the end of it, and returns me some XML. I am using .NET 4 and the Entity Framework and when I do a function import of this stored procedure and try to call it through the Entity Framework it truncates the return at 2033 characters. I swapped the Entity Framework for a traditi...