March 10, 2009 at 12:24 am
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.
March 10, 2009 at 7:55 am
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...
March 10, 2009 at 9:01 pm
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.
March 11, 2009 at 5:51 am
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."
March 11, 2009 at 10:12 am
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.
March 11, 2009 at 3:35 pm
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
March 11, 2009 at 7:24 pm
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