Convert SQL table to XML

Question

Convert SQL table to XML

devesh.hassani

SSChasing Mays

Points: 600
More actions
October 5, 2015 at 3:21 am

#321648

Hi,
I want to convert table into following xml format. Sample two records is given below. Any help would be appreciated.
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">Test001</Data>
</Cell>
</ROW>
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64857</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">Test002</Data>
</Cell>
</ROW>
Thanks,
DH
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

You have given far too little information to go on, if you can post up the schema and sample data from your table it would help someone give you a specific answer.

But have a read of this, it explains what you need to know about returning queries as XML:

https://msdn.microsoft.com/en-us/library/ms178107.aspx">

https://msdn.microsoft.com/en-us/library/ms178107.aspx

MCITP SQL 2005, MCSA SQL 2012

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 2

Table schema is below. It contains three columns only

Column AColumn BColumn C

XXXXXXXX64856Test001

XXXXXXXX64857Test002

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

devesh.hassani (10/5/2015)
Table schema is below. It contains three columns only
Column AColumn BColumn C
XXXXXXXX64856Test001
XXXXXXXX64857Test002

Please see the link in my signature on posting code and data for the best help.

E.g, your sample would look something like this

CREATE TABLE #Temp

(

[Column A] VARCHAR(10),

[Column B] VARCHAR(5),

[Column C] VARCHAR(10)

)

INSERT INTO #Temp VALUES

('XXXXXXXXXX','64856','Test001'),

('XXXXXXXXXX','64857','Test002')

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 4

Thanks! Anthony.

Correct my sample data will be as below.

CREATE TABLE #Temp

(

[Column [A] VARCHAR(10),

[Column VARCHAR(5),

[Column [C] VARCHAR(10)

)

INSERT INTO #Temp VALUES

('XXXXXXXXXX','64856','Test001'),

('XXXXXXXXXX','64857','Test002')

RTaylor2208 SSChampion Points: 13298 More actions · Answer 5

XML is not a strength of mine but I think this is possible using the FOR XML EXPLICIT command

https://technet.microsoft.com/en-us/library/aa226532(v=sql.80).aspx

I don't have time to try and do this with your sample data right now, but I'll try and get back to this later today.

MCITP SQL 2005, MCSA SQL 2012

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 6

Thanks! Taylor...Will look forward for your post. I tried using FOR XML Explicit, however, could not get the required XML structure...

RTaylor2208 SSChampion Points: 13298 More actions · Answer 7

Having done some testing this is definitely outside my level of knowledge with XML. Hopefully someone else can post up a solution.

MCITP SQL 2005, MCSA SQL 2012

Dee-ZA Old Hand Points: 347 More actions · Answer 8

The code below might help:

CREATE TABLE #Temp

(

[Column A] VARCHAR(10),

[Column B] VARCHAR(5),

[Column C] VARCHAR(10)

)

INSERT INTO #Temp VALUES

('XXXXXXXXXX','64856','Test001'),

('XXXXXXXXXX','64857','Test002')

SELECT [Column A] AS ColumnA

,[Column B] AS ColumnB

,[Column C] AS ColumnC

FROM #Temp

FOR XML PATH ('Row')

RTaylor2208 SSChampion Points: 13298 More actions · Answer 9

itumelengd (10/5/2015)
The code below might help:
CREATE TABLE #Temp
(
[Column A] VARCHAR(10),
[Column B] VARCHAR(5),
[Column C] VARCHAR(10)
)
INSERT INTO #Temp VALUES
('XXXXXXXXXX','64856','Test001'),
('XXXXXXXXXX','64857','Test002')
SELECT [Column A] AS ColumnA
,[Column B] AS ColumnB
,[Column C] AS ColumnC
FROM #Temp
FOR XML PATH ('Row')

Unfortunatly the representation you get back doesn't match that requested:

This is what they are looking for:

<Row>

<Cell ss:StyleID="s22">

<Data ss:Type="String">XXXXXXXXXXXXX</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">64856</Data>

</Cell>

<Cell ss:StyleID="s22">

<Data ss:Type="String">Test001</Data>

</Cell>

</ROW>

This is what your code brings back:

<Row>

<ColumnA>XXXXXXXXXX</ColumnA>

<ColumnB>64856</ColumnB>

<ColumnC>Test001</ColumnC>

</Row>

<Row>

<ColumnA>XXXXXXXXXX</ColumnA>

<ColumnB>64857</ColumnB>

<ColumnC>Test002</ColumnC>

</Row>

As you pointed out using FOR XML PATH seems to get closer to whats needed I too could not get the same format:

select'StyleID="s22"' AS '@ss',

ColumnA as Data1,

ColumnB as Data2,

ColumnC as Data3

FROM #Temp t

FOR XML PATH('Cell'), root('Row')

Returns:

<Row>

<Cell ss="StyleID="s22"">

<Data1>XXXXXXXXXX</Data1>

<Data2>64856</Data2>

<Data3>Test001</Data3>

</Cell>

<Cell ss="StyleID="s22"">

<Data1>XXXXXXXXXX</Data1>

<Data2>64857</Data2>

<Data3>Test002</Data3>

</Cell>

</Row>

The extra nesting should be possible using correlated subqueries in place of the ColumnX as DataX statements, but I still could not get the format matching although the nesting looked closer to what was requested.

MCITP SQL 2005, MCSA SQL 2012

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 10

devesh.hassani (10/5/2015)
Hi,
I want to convert table into following xml format. Sample two records is given below. Any help would be appreciated.
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">Test001</Data>
</Cell>
</ROW>
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64857</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">Test002</Data>
</Cell>
</ROW>
Thanks,
DH

It would appear that you're trying to export "spreadsheet data". Why not just do a simple export of TSV data instead of jumping through the XML and "format every cell" hoop? It would be a whole lot easier on "the pipe".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

devesh.hassani SSChasing Mays Points: 600 More actions · Answer 11

Thanks! for the suggestion Jeff.

Can you please explain how can we do the export from SQL as i am not sure on the process.

Thanks,

DH

Ant-Green SSC Guru Points: 113507 More actions · Answer 12

How often does this need to run?

Very ad-hoc you could look at the Import/Export Wizard

Everyday/hour etc I would look at SSIS

Also thanks to Jeff, hadn't seen spreadsheet XML like that will be one ingrained into the recesses of my mind

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

IMHO trying to use XML to format this is more trouble than it's worth.

A bit of VARCHAR works nicely though.

CREATE TABLE #Temp

(

[Column A] VARCHAR(10),

[Column B] VARCHAR(5),

[Column C] VARCHAR(10)

)

INSERT INTO #Temp VALUES

('XXXXXXXXXX','64856','Test001'),

('XXXXXXXXXX','64857','Test002');

SELECT

' <Row>

<Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column A] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

+ ' <Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column B] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

+ ' <Cell ss:StyleID="s22"><Data ss:Type="String">' + ISNULL( CAST( [Column C] AS VARCHAR), '') + '</Data></Cell>'+CHAR(13)

+ ' </Row>'

FROM #Temp;

However, I would recommend hitting Jeff up for some alternative ideas.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 14

devesh.hassani (10/5/2015)
Thanks! for the suggestion Jeff.
Can you please explain how can we do the export from SQL as i am not sure on the process.
Thanks,
DH

I guess the first question to ask is how would you have done the export of XML to a file (presuming you wanted a file)? As soon as I know that, I can help using TSVs to export instead of XML.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)