November 29, 2013 at 5:17 am
Goal:
Display the data of xml in a customized table.
The requested table list will be
user firstname lastname hour projectname sex
-------------------------------------------------------------------------
userTime sara brown 20 null null
userProject jessica black null Melissa null
userProject Jim west null Sakura null
userSex robert lake null null male
etc....
Problem:
In the hiearchy I have three different level that is <userTime>, <userProject> and <userSex> that are subset of and I want them and other column to display in the customized table with the chronological order that is similiar to the xml data.
Information:
*The data for userTime, userProject and userSex in the XML is random
*The list in the XML is huge.
DECLARE @xml XML
SET @xml =
(
SELECT * FROM OPENROWSET
(
BULK 'C:\server\xml\test.xml', SINGLE_CLOB
) AS xmlData
)
SELECT
firstname = Events.value('(firstname)[1]', 'VARCHAR(100)'),
lastname = Events.value('(lastname)[1]', 'VARCHAR(100)'),
hour = Events.value('(hour)[1]', 'VARCHAR(100)')
FROM @XML.nodes('/users/userTime') as XTbl(Events)
<users>
<userTime>
<firstname>sara</firstname>
<lastname>brown</lastname>
<hour>20</hour>
</userTime>
<userProject>
<firstname>jessica</firstname>
<lastname>black</lastname>
<projectname>Melissa</projectname>
</userProject>
<userProject>
<firstname>Jim</firstname>
<lastname>west</lastname>
<projectname>Sakura</projectname>
</userProject>
<userSex>
<firstname>robert</firstname>
<lastname>lake</lastname>
<sex>male</sex>
</userSex>
<userTime>
<firstname>Britany</firstname>
<lastname>lake</lastname>
<hour>20</hour>
</userTime>
<userTime>
<firstname>sara</firstname>
<lastname>brown</lastname>
<hour>20</hour>
</userTime>
</users>
November 29, 2013 at 5:17 pm
I'm no Ninja at XML but I have to say that's some of the most poorly formed XML I've ever seen. Is there any way to get the provider to clean that up so that it's actually useful and is properly organized by hierarchy?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 2, 2013 at 1:44 am
Also not exactly a wizard at XML, so I have no idea how this'll scale.
--== SAMPLE DATA ==--
DECLARE @XML XML = '<users>
<userTime>
<firstname>sara</firstname>
<lastname>brown</lastname>
<hour>20</hour>
</userTime>
<userProject>
<firstname>jessica</firstname>
<lastname>black</lastname>
<projectname>Melissa</projectname>
</userProject>
<userProject>
<firstname>Jim</firstname>
<lastname>west</lastname>
<projectname>Sakura</projectname>
</userProject>
<userSex>
<firstname>robert</firstname>
<lastname>lake</lastname>
<sex>male</sex>
</userSex>
<userTime>
<firstname>Britany</firstname>
<lastname>lake</lastname>
<hour>20</hour>
</userTime>
<userTime>
<firstname>sara</firstname>
<lastname>brown</lastname>
<hour>20</hour>
</userTime>
</users>';
--== SOLUTION HERE ==--
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT
name,value,id,CASE WHEN value IS NULL THEN 1 ELSE 0 END AS [group]
INTO #testEnvironment
FROM (SELECT
xmlData.value('local-name(.)', 'nvarchar(max)'),
xmlData.value('(./text())[1]', 'nvarchar(max)'),
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM @XML.nodes('//*') [XML](xmlData)
WHERE xmlData.value('local-name(.)', 'nvarchar(max)') <> 'users'
)a(name,value,id)
OPTION (MAXDOP 1,RECOMPILE);
SELECT
NULLIF(MAX(CASE WHEN name IN ('userTime','userProject','userSex') THEN name ELSE '' END),'') AS ,
NULLIF(MAX(CASE WHEN name = 'firstname' THEN value ELSE '' END),'') AS [firstname],
NULLIF(MAX(CASE WHEN name = 'lastname' THEN value ELSE '' END),'') AS [lastname],
NULLIF(MAX(CASE WHEN name = 'hour' THEN value ELSE '' END),'') AS [hour],
NULLIF(MAX(CASE WHEN name = 'projectname' THEN value ELSE '' END),'') AS [projectname],
NULLIF(MAX(CASE WHEN name = 'sex' THEN value ELSE '' END),'') AS [sex]
FROM (SELECT name,value,id,
CASE WHEN value IS NULL THEN id-[group] ELSE [group] END AS [group]
FROM (SELECT name,value,id,
ROW_NUMBER() OVER(ORDER BY id)-
DENSE_RANK() OVER(PARTITION BY [group] ORDER BY id) AS [group]
FROM #testEnvironment
)a
)b
GROUP BY [group];
Produces: -
user firstname lastname hour projectname sex
------------- ------------- ------------- ------------- ------------- -------------
userTime sara brown 20 NULL NULL
userProject jessica black NULL Melissa NULL
userProject Jim west NULL Sakura NULL
userSex robert lake NULL NULL male
userTime Britany lake 20 NULL NULL
userTime sara brown 20 NULL NULL
I realise that there's a bit more work to do there with distinct, but you might be able to get something to work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply