SQL Server Performance
  1. SQL2000DBA New Member

    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


  2. DilliGrg Member

    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

Share This Page