XML Manipulation Problem

  • Is there a way convert from this xml format:

    <sample>

    <data>test1, test2 , test3</data>

    <data>test10, test20 , test30</data>

    <data>test11, test21 , test31</data>

    </sample>

    to this xml format?

    <sample>

    <data>

    <entry id="code">test1</entry>

    <entry id="tablename">test2</entry>

    <entry id="columnvalue">test3</entry>

    </data>

    <data>

    <entry id="code">test10</entry>

    <entry id="tablename">test20</entry>

    <entry id="columnvalue">test30</entry>

    </data>

    <data>

    <entry id="code">test11</entry>

    <entry id="tablename">test21</entry>

    <entry id="columnvalue">test31</entry>

    </data>

    </sample>

    Any help with sample code is greatly appreciated.

  • Hi,

    based on the split function as stated in the following post

    http://www.sqlservercentral.com/Forums/FindPost451474.aspx (which requires a tally table)

    I did some modification to that function to cover the order of appearence in the list as well:

    CREATE FUNCTION [dbo].[fn_Split]

    (

    @ItemList NVARCHAR(4000),

    @delimiter CHAR(1))

    RETURNS @IDTable TABLE (Item VARCHAR(50),

    Attrib VARCHAR(50))

    AS

    BEGIN

    INSERT INTO @IDTable

    SELECT SUBSTRING(@ItemList+@delimiter, N, CHARINDEX(',', @ItemList+@delimiter, N) - N),

    CASE ROW_NUMBER() OVER (ORDER BY Tally.N)

    WHEN 1 THEN 'code'

    WHEN 2 THEN 'tablename'

    WHEN 3 THEN 'columnvalue'

    ELSE 'unknown'

    END

    FROM dbo.Tally

    WHERE N <= LEN(@ItemList)

    AND SUBSTRING(@delimiter + @ItemList,

    N, 1) = @delimiter

    ORDER BY N

    RETURN

    END

    With this function I used the following code:

    DECLARE @xmlDoc XML,

    @docHandle INT

    DECLARE @tbl TABLE (Data VARCHAR(50))

    -- sample data

    SET @xmlDoc =

    '[sample]

    [data]test1, test2 , test3[/data]

    [data]test10, test20 , test30[/data]

    [data]test11, test21 , test31[/data]

    [/sample]'

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDoc

    ;WITH data(parentid,text) AS

    (SELECT parentid,text FROM OPENXML(@docHandle, N'/sample/data') WHERE TEXT IS NOT NULL)

    SELECT parentid, attrib AS id, LTRIM(Item) AS value

    FROM data

    CROSS APPLY

    dbo.fn_split(data.text,',') AS entry

    FOR XML AUTO

    EXEC sp_xml_removedocument @docHandle

    The result is almost what you need. I think from this point you can change the above FOR XML AUTO statement to the output format you need.

    [data parentid="2"]

    [entry id="code" value="test1" /]

    [entry id="tablename" value="test2 " /]

    [entry id="columnvalue" value="test3" /]

    [/data]

    [data parentid="3"]

    [entry id="code" value="test10" /]

    [entry id="tablename" value="test20 " /]

    [entry id="columnvalue" value="test30" /]

    [/data]

    [data parentid="4"]

    [entry id="code" value="test11" /]

    [entry id="tablename" value="test21 " /]

    [entry id="columnvalue" value="test31" /]

    [/data]

    Note: The XML-format needed to be changed slightly in order to show up in the post...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    Thank you for this sample code, though I was just wondering about the tally table, can you show the details inside this table? I couldn't find one even on the link you provided.

    Thanks a lot.

  • See this article for Tally Table information:

    http://www.sqlservercentral.com/articles/TSQL/62867/

    🙂

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Hello James,

    I'm sorry that I didn't include some more information about the Tally table in the first place...

    The article mentioned above is one of my favorites on how to use a tally table.

    I probably should have described a little more detailed what the code is supposed to do:

    1) function [fn_Split]

    The split function is used to extract the single items out of a string like "test1, test2 , test3", where the separator is added as the second argument.

    You can find a few ways of doing string splitting in this forum by typing "split function" as a criteria in the search box on the upper right corner on this site.

    This will give you links to some possible solutions to do string splitting.

    Some of the solutions will use loops (e.g. WHILE or CURSOR), others will use some kind of a Tally table.

    Jeff's article describes the advantages of a tally table in a way where's nothing more to say than "use it" - at least from my point of view.. 🙂

    So, instead of using a cursor to loop through the string the function uses a single SELECT statement.

    The function also uses the ROW_NUMBER() function of SS2K5 to add the order of appearance of an extracted element in the string.

    This generated row number then is replaced with the tag name you wanted to have in the output.

    So, if you'd like to add a 4th element to the list, you can simply add the tag name of this element by adding another WHEN clause.

    The result of this function will give you a table with the string elements and the tag name of each element.

    You can call that function separately:

    SELECT * FROM dbo.fn_split('test1, test2 , test3',',')

    The result will be

    Item | Attrib

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

    test1 | code

    test2 | tablename

    test3 | columnvalue

    Please note the leading blanks of the Item's in 2nd and 3rd line. Theses will be removed with the LTRIM(Item) statement in the SELECT clause later on.

    2) Using a table valued function together with the split function above and the CROSS APPLY operator will result in the result table as shown in my previous post.

    If you'd like to see the result set in a standard table structure, just remove the FOR XML AUTO statement.

    Note: For details on CROSS APPLY see BOL:

    The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

    I hope the additional information will make it a little easier for you.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hello James,

    I'm sorry for confusing you but I have to correct the code I provided - even though it seems to work.

    Reason:

    I just run across Barry's statement regarding the use of OPENXML (like I did in my first post). (http://www.sqlservercentral.com/Forums/FindPost672137.aspx"> http://www.sqlservercentral.com/Forums/FindPost672137.aspx )

    Since Barry's posts usually are more than helpful I had to rethink my first post and I'd like to offer you a second way with using XQuery instead of OPENXML. (aside of that I have to rethink all of my OPENXML coding currently in use... :crying: )

    The code replaces the second coding part in my first post (the split function didn't change). The result is (almost) identical. The only difference is the [parentid] that starts with 1 instead of 2 (as in my first post).

    DECLARE @xmlDoc XML

    -- sample data

    SET @xmlDoc =

    '[sample>

    [data]test1, test2 , test3[/data]

    [data]test10, test20 , test30[/data]

    [data]test11, test21 , test31[/data]

    [/sample]'

    ;WITH data(parentid,text) AS

    (SELECT

    -- begin modified section

    ROW_NUMBER() OVER(ORDER BY CAST(T.c.query('string(.)') AS VARCHAR(50))) AS parentid,

    CAST(T.c.query('string(.)') AS VARCHAR(50)) AS text

    FROM @xmlDoc.nodes('/sample/data') T(c))

    -- end modified section

    SELECT parentid, attrib AS id, LTRIM(Item) AS value

    FROM data

    CROSS APPLY

    dbo.fn_split(data.text,',') AS entry

    FOR XML AUTO



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank you Lutz and jcrawf02, for supplying me this helpful information. I really appreciate it! it works!

    Best regards,

    James

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply