Generate XML from Table

I have a table like this:

ID Name Column1 Column2 Column3 1 ABC 202.2 1500 34000 2 IJK 104 10000 27000

I want to generate XML like this:

<doc> <record ID="1" Name="ABC" Column1="202.2" Column2="15000" Column3="34000" /> <record ID="2" Name="IJK" Column1="104" Column2="10000" Column3="27000" /> </doc>

I have got some clue from this forum post and used this code:

CREATE TABLE #tmp (column1 VARCHAR(20), column2 VARCHAR(20), column3 VARCHAR(20)) INSERT INTO #tmp VALUES ( 'data1', 'data2', 'data3' ) INSERT INTO #tmp VALUES ( 'data11', 'data21', 'data31' ) -- FOR XML PATH with ELEMENTS will automatically unpivot the data for you -- Then reshape your XML using nested FLWOR loops SELECT ( SELECT * FROM #tmp t FOR XML PATH, ELEMENTS, TYPE ).query(' for $e in row return <row>{ for $f in $e/* return <field name="{local-name($f)}">{data($f)}</field> } </row> ')

I tried the following modified version:

SELECT (SELECT * FROM cte_temp t FOR XML PATH, ELEMENTS, TYPE) .query('for $e in row return <doc> { for $f in $e return <record {local-name($f)}="{data($f)}" /> } </doc>')

But I'm getting error:

XQuery [query()]: Invalid source character 0x7b found in an identifier near 'return'.


Why you trying to get fancy.

Select * from #tmp as record
FOR XML AUTO, root('doc')

Category:tsql Time:2011-12-23 Views:1

Related post

  • Generate XML-formatted table schema from SQL Server 2005? 2010-08-13

    I was wondering how I might take a table schema in SQL Server and generate an XML document from it. The ideal would be if I passed in my database name (“SalesOrders”) and an XML doc comes back reading something like: <table=”SalesOrders”> <c

  • Generate XML document in PL/SQL from Oracle tables 2009-11-30

    I have to generate XML documents in PL/SQL from some tables in Oracle. I have never done this before, and I have found there seem to be a few main ways to do it: xmldom API xml functions (such as xmlelement, xmlagg, xmlroot) dbms_xmlgen functions The

  • linq to sql/xml - generate xml for linked tables 2010-11-05

    i have alot of tables with alot of columns and want to generate xml using linq without having to specify the column names. here's a quick example: users --------------- user_id name email user_addresses --------------- address_id user_id city state t

  • generate XML data for a parent child tables 2011-12-08

    I am trying to generate XML from parent child tables. Here is the XML format I am trying to achieve. The PHP code only works for one table. I am not sure how to generate XML from a child table. I appreciate any help. <SalesOrder> <Sales>

  • Generate XML in proper syntax from SQL Server table 2012-02-12

    How to write a SQL statement to generate XML like this <ROOT> <Production.Product> <ProductID>1 </ProductID> <Name>Adjustable Race</Name> ........ </Production.Product> </ROOT> Currently I am getting th

  • What is the best way to generate XML from the data in the database? 2009-02-28

    If I have thousands of hierarchical records to take from database and generate xml, what will be the best way to do it with a good performance and less CPU utilization? --------------Solutions------------- You can output XML directly from SQL Server

  • Safely generating XML in Lua 2009-08-12

    I'm writing a Lua application that generates an Atom feed. Right now, I'm generating XML "by hand" -- writing strings to files. This doesn't seem like the best way, although it may be. I am getting nervous about having the escaping exactly right. Has

  • Generate xml from csv data in conformance with given xsd schema 2009-10-27

    I have an xml schema and csv data to generate corresponding xml files. I found this, how to generate xml files from given CSV file. But with my schema, it's always the same mapping because of the element. So the last 5 columns are mapped according to

  • How to generate XML from an Excel VBA macro? 2010-05-04

    So, I've got a bunch of content that was delivered to us in the form of Excel spreadsheets. I need to take that content and push it into another system. The other system takes its input from an XML file. I could do all of this by hand (and trust me,

  • How to Generate XML from Database 2010-05-05

    I am fetching data from two tables CARRIER_IFTA ,IFTA_NAME. My Select Query is like below.. SELECT t1.IFTA_LICENSE_NUMBER,t1.IFTA_BASE_STATE,t2.NAME_TYPE,t2.NAME from CARRIER_IFTA t1 inner join IFTA_NAME t2 on t1.IFTA_LICENSE_NUMBER=t2.IFTA_LICENSE_N

  • Optimization on huge generating xml? 2010-11-08

    Currently, I'm developing a rails app that are heavy generating xml for restful webservice. My xml representation of web service use nokogiri gem to generates xml format that match expected format from client. But the problem is data is quite big aro

  • Generate XML from PHP via SQL 2011-03-21

    i have a error when running this code .. i run fine when there is no special characteres in database. if its has special characters then i got the error please solve me i am very thankful. Following Error are occured when any special charachers in da

  • generate xml and xsl from php 2011-06-04

    i am trying to generate xml from mysql database. The application does not need to know the tables that exist in the database. the function is such that you enter the table name and it generates the xml for it. The function works perfect but i need to

  • How to generate xml file with specific structure from datatables? 2011-12-06

    Q : According to some choices of the user, i will get set of Datatables from the database. i have three datatabels:Teachers,Subjects,ClassRooms and I want an xml file with the following structure: <timetable importtype="database" options="idprefix

  • How to handle curly apostrphes and curly quotes in PHP when generating xml? 2012-01-09

    My DB has some text which is probably copied and pasted from word document contains some curly quotes and curly apostrophes. PHP code is generating XML data/document with this text as one of its XML element. This is the error I'm getting when I try t

  • Generate XML from Oracle PL/SQL multilevel collection 2012-02-28

    I am quite amateur on Oracle DB and PL/SQL in general, but need to interface with a Oracle DB nevertheless. I have a PL/SQL Stored Procedure that has an output parameter which is a multilevel collection: declare TYPE level_a_type IS RECORD ( text var

  • How can I query SQL Server and generate XML with column names and values as attributes 2012-03-03

    I'm trying to figure out how to query a table that generates xml that looks like below: (this is a sample from the AdventureWorks database. I can get the column names as the elements easy enough, but is it possible to make both the column name and va

  • Encoding Error in PHP script to generate XML 2012-04-18

    I'm having problems with my PHP file that generates XML from MySQL database. The code is below <?php require("decibelone_dbinfo.php"); function parseToXML($htmlStr) { $xmlStr=str_replace('<','&lt;',$htmlStr); $xmlStr=str_replace('>','

  • Problem with generating XML for Adobe Central 2013-02-21

    I have a BizTalk application to generate xml files for processing through Adobe Central. the top line of the XMl file is generated in a class library using string temp = @"<?xml version=""1.0"" encoding=""utf-8""?><?jetform ^JOB " + OutFormA

Copyright (C), All Rights Reserved.

processed in 0.487 (s). 13 q(s)