XML Query

Hi,

I need small help related to Xquery. Please see below example.

DECLARE @REF XML
SET @REF='<StoreCode>
<ID>1</ID>
<ID>2</ID>
<ID>3</ID>
<ID>4</ID>
<ID>5</ID>
</StoreCode>'

SELECT CONVERT(VARCHAR(20),@REF.query('data(StoreCode/ID[.])'))

I am getting 1 2 3 4 5 in one row. I need to convert this in below format

1
2
3
4
5

How can i achieve this.Note that i can't change the XML format.

Thanks and Regards
Ravi K

quote:Originally posted by SQL2000DBA

Hi,

I need small help related to Xquery. Please see below example.

DECLARE @REF XML
SET @REF='<StoreCode>
<ID>1</ID>
<ID>2</ID>
<ID>3</ID>
<ID>4</ID>
<ID>5</ID>
</StoreCode>'

SELECT CONVERT(VARCHAR(20),@REF.query('data(StoreCode/ID[.])'))

I am getting 1 2 3 4 5 in one row. I need to convert this in below format

1
2
3
4
5

How can i achieve this.Note that i can't change the XML format.

Thanks and Regards
Ravi K

How about capturing the parsed result from xml to temp table and use cross-tab report to retrieve desired result set from this temp table?

Thanks,
DilliGrg