Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: Formatting data with FOR XML and sp_makewebtask?

    Hi all,

    I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

    My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

    Cheers,
    Christian


    ----------------------


    --- sp_makewebtask ---
    EXEC sp_makewebtask
    @outputfile = c:\temp\output.xml,
    @query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
    @templatefile ='c:\temp\template.tpl'


    --- c:\temp\template.tpl ---
    <?xml version="1.0" encoding="UTF-8"?>
    <Data>
    <%begindetail%>
    <%insert_data_here%>
    <%enddetail%>
    </Data>


    --- c:\temp\output.xml ---
    <?xml version="1.0" encoding="UTF-8"?>
    <Data>
    <Subdata>
    <Subdata1>455</Subdata1>
    <Subdata2>12312</Subdata2>
    <Subdata3>1</Subdata3>
    </Subdata>
    <Subdata>
    <Subdata1>435345</Subdata1>
    <Subdata2>675</Subdata2>
    <Subdata3>1</Subdata3>
    </Subdata>
    <Subdata>
    <Subdata1>133323</Subdata1>
    <Subdata2>976</Subdata2>
    <Subdata3>1</Subdata3>
    </Subdata>
    </Data>


    --- Prefered XML output ---
    <?xml version="1.0" encoding="UTF-8"?>
    <Data Subdata3="1">
    <Subdata>
    <Subdata1>455</Subdata1>
    <Subdata2>12312</Subdata2>
    </Subdata>
    <Subdata>
    <Subdata1>435345</Subdata1>
    <Subdata2>675</Subdata2>
    </Subdata>
    <Subdata>
    <Subdata1>133323</Subdata1>
    <Subdata2>976</Subdata2>
    </Subdata>
    </Data>

  2. #2
    Join Date
    Aug 2004
    Posts
    4

    Solved it!

    Nevermind, solved it using FOR XML EXPLICIT.

    /Christian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •