Generating XML string (SQL Server 2005)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Generating XML string (SQL Server 2005)

Author

Topic

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 00:08:40

Hi,
I want to generate the xml string in sql server 2005, this string can have multiple row/cell/Data tags

Right now I'm using query
select CompanyID,CompanyName from tblCompay
for xml auto
and its not giving me the correct output as I need
so how can I get the out put as shown below in sql server 2005

<Row>
<Cell>
<Data>CompanyID</Data>
<Data>CompanyName</Data>
</Cell>
</Row>

Thanks in advance

Sachin Hingole

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-25 : 01:28:45

How about XML PATH?


select CompanyID,CompanyName from tblCompay
for xml PATH

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 02:05:39

quote:
Originally posted by Mangal Pardeshi

How about XML PATH?

select CompanyID,CompanyName from tblCompay
for xml PATH

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

No its not giving desired output as I need
The out put is like belwo text
<row>
<CompanyID>3396</CompanyID>
<CompanyName>CompName</CompanyName>
</row>

Sachin Hingole

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-25 : 02:25:35

Hi,
XML PATH giving exactly the same output..
I got this output with some sample data

<row><orderid>1</orderid><quantity>10</quantity></row><row><orderid>1</orderid><quantity>15</quantity></row>

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 03:06:36

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 100, 'Peso'

SELECT CAST(REPLACE((
SELECT ID AS Data,
Name AS DataX
FROM @Sample
FOR XML PATH('Cell'),
ROOT('Row'),
ELEMENTS
), 'DataX>', 'Data>') AS XML)[/code]

E 12°55'05.63"
N 56°04'39.26"

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 04:01:37

quote:
Originally posted by Mangal Pardeshi

Hi,
XML PATH giving exactly the same output..
I got this output with some sample data

<row><orderid>1</orderid><quantity>10</quantity></row><row><orderid>1</orderid><quantity>15</quantity></row>

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

The expected out put is as below
<Table>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Table>
Sachin Hingole

jbp_j
Starting Member

24 Posts

Posted - 2009-02-25 : 04:11:59

HI,

Try this one also.

select cast(replace(replace((select ID,Name from companies for xml path('row.cell')),'<row.','<row> <'),'</row.cell>','</cell></row>')as xml)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 04:23:55

Did you try the suggestion made 02/25/2009 : 03:06:36 ?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:00:46

Where is the sample data to work with?

E 12°55'05.63"
N 56°04'39.26"

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 06:38:15

quote:
Originally posted by Peso

Did you try the suggestion made 02/25/2009 : 03:06:36 ?

E 12°55'05.63"
N 56°04'39.26"

Ya, I have tried its as I need but still I want to customize as below if u have any Idea can u please suggest me.
thanks in advance
<Table>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Row>
<Cell ss:MergeAcross="1" ss:StyleID="s24">
<Data ss:Type="String">CompanyID</Data>
<Data ss:Type="String">CompanyName</Data>
</Cell>
</Row>
<Table>

Sachin Hingole

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 06:44:00

Give me some sample data to work with...

E 12°55'05.63"
N 56°04'39.26"

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 07:03:13

quote:
Originally posted by Peso

Give me some sample data to work with...

E 12°55'05.63"
N 56°04'39.26"

DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 100, 'Peso'
union
SELECT 101, 'Test1'
union
SELECT 102, 'Test2'
union
SELECT 103, 'Test3'
union
SELECT 104, 'Test4'
union
SELECT 105, 'Test5'

Sachin Hingole

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 07:15:12

[code]DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 100, 'Peso'
union
SELECT 101, 'Test1'
union
SELECT 102, 'Test2'
union
SELECT 103, 'Test3'
union
SELECT 104, 'Test4'
union
SELECT 105, 'Test5'

declare @a varchar(max)

set @a = (
SELECT ID AS DataX,
Name AS DataY
FROM @Sample
FOR XML PATH('Row'),
ROOT('Table'),
ELEMENTS
)

select @a = replace(@a, '<Row>', '<Row><Cell ss:MergeAcross="1" ss:StyleID="s24">'),
@a = replace(@a, '<DataX>', '<Data ss:Type="String">'),
@a = replace(@a, '</DataX>', '</Data>'),
@a = replace(@a, '<DataY>', '<Data ss:Type="String">'),
@a = replace(@a, '</DataY>', '</Data>'),
@a = replace(@a, '</Row>', '</Cell></Row>')

select @a[/code]

E 12°55'05.63"
N 56°04'39.26"

sachin.hingole
Starting Member

15 Posts

Posted - 2009-02-25 : 07:49:35

quote:

Originally posted by Peso

DECLARE @Sample TABLE
(
ID INT,
Name VARCHAR(20)
)

INSERT @Sample
SELECT 100, 'Peso'
union 
SELECT 101, 'Test1'
union 
SELECT 102, 'Test2'
union 
SELECT 103, 'Test3'
union 
SELECT 104, 'Test4'
union 
SELECT 105, 'Test5'

declare @a varchar(max)

set @a = (
SELECT	ID AS DataX,
	Name AS DataY
FROM	@Sample
FOR XML	PATH('Row'),
	ROOT('Table'),
	ELEMENTS
)

select	@a = replace(@a, '<Row>', '<Row><Cell ss:MergeAcross="1" ss:StyleID="s24">'),
	@a = replace(@a, '<DataX>', '<Data ss:Type="String">'),
	@a = replace(@a, '</DataX>', '</Data>'),
	@a = replace(@a, '<DataY>', '<Data ss:Type="String">'),
	@a = replace(@a, '</DataY>', '</Data>'),
	@a = replace(@a, '</Row>', '</Cell></Row>')

select @a

E 12°55'05.63"
N 56°04'39.26"

Thanks a lot its the same thing I need.

Sachin Hingole

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources