SQL Server Performance Forum – Threads Archive
XML Query
Hi, I need small help related to Xquery. Please see below example. DECLARE @REF XMLSET @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,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
DilliGrg
]]>