InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:30:48 GMT20http://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxYes, I like that idea. HahaThanks both for your time. I think I can work with that.RegardsFri, 01 Mar 2013 09:34:55 GMTNickBalaamhttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspx[quote][b]NickBalaam (3/1/2013)[/b][hr]Thanks for the heads up. Unfortunately this is not our own XML, it's comes to us from a 3rd party and we have no control over the structure. Ideally it would have had something like <skill id=1>tSQL</skill> but I can't really do much about it :-([/quote]Heh... actually, you can. Invite them to dinner. A nice pork chop dinner. To make it so they understand, tie them to the chair and feed them the porchops... at point blank range with a Wrist Rocket. :-D You'll get your point across.If these people are providing a "service", they need to provide it correctly. If they're a customer, the need to help you help them. Since the answer is always "No" unless you ask, contact the 3rd part and tell them they're doing it wrong and you want it fixed!Fri, 01 Mar 2013 09:08:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxAs Jeff points out, the xml isn't ideal which is true, but I have come up with something that [u]makes a lot of assumptions![/u]. Mainly around the ordering of the xml which if changes, will break this so I give no assurances to this code :-D[code="sql"]DECLARE @xml XMLSET @xml = '<employees><employee><emp_id>1</emp_id><emp_name>Bob</emp_name><skills> <skills_id>1</skills_id> <skills_id>tsql</skills_id> <skills_id>2</skills_id> <skills_id>SSRS</skills_id> <skills_id>3</skills_id> <skills_id>SSAS</skills_id> <skills_id>4</skills_id> <skills_id>SSIS</skills_id> <skills_id>5</skills_id> <skills_id>Replication</skills_id></skills></employee><employee><emp_id>2</emp_id><emp_name>Frank</emp_name><skills> <skills_id>1</skills_id> <skills_id>tsql</skills_id> <skills_id>2</skills_id> <skills_id>SSRS</skills_id> <skills_id>3</skills_id> <skills_id>SSAS</skills_id></skills></employee></employees>'SELECT @xml = @xml.query('<employees>{ for $x in //employee return <employee emp_name="{$x/emp_name/text()}" emp_id="{$x/emp_id/text()}"> { for $y in $x/skills/skills_id[number(text()[1]) > 0] return <skill id="{data($y)}" value="{data($x/skills/skills_id[. >> $y][1])}"/> } </employee>}</employees>')SELECT e.c.value('@emp_id', 'int') AS emp_id , e.c.value('@emp_name', 'varchar(50)') AS emp_name , s.c.value('@id', 'int') AS skills_id , s.c.value('@value', 'varchar(50)') AS skillFROM @xml.nodes('/employees/employee') e(c)CROSS APPLY e.c.nodes('skill') AS s(c)[/code]Returns:[code="plain"]emp_id emp_name skills_id skill1 Bob 1 tsql1 Bob 2 SSRS1 Bob 3 SSAS1 Bob 4 SSIS1 Bob 5 Replication2 Frank 1 tsql2 Frank 2 SSRS2 Frank 3 SSAS[/code]Fri, 01 Mar 2013 08:33:22 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxThanks for the heads up. Unfortunately this is not our own XML, it's comes to us from a 3rd party and we have no control over the structure. Ideally it would have had something like <skill id=1>tSQL</skill> but I can't really do much about it :-(Fri, 01 Mar 2013 07:37:46 GMTNickBalaamhttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspx[quote][b]NickBalaam (3/1/2013)[/b][hr]Hi, thanks for taking time to look at that. Ideally I'd like to see....Emp_id Skill_ID Skill_Name1 1 tSQL1 2 SSRS1 3 SSAS1 4 SSIS1 5 Replication2 1 tSQL2 2 SSRSetc[/quote]Just a heads up on that. The XML you were provided is "poorly formed" in that, except by position in the file, there's no logical manner to associate a particular skill ID with the correct name. You can't rely on the position in the file for these sorts of things because it's just not reliable.Fri, 01 Mar 2013 07:31:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxHi, thanks for taking time to look at that. Ideally I'd like to see....Emp_id Skill_ID Skill_Name1 1 tSQL1 2 SSRS1 3 SSAS1 4 SSIS1 5 Replication2 1 tSQL2 2 SSRSetcFri, 01 Mar 2013 07:23:45 GMTNickBalaamhttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxHere is a quick example to shred that xml to a flat table:[code="sql"]DECLARE @xml XMLSET @xml = '<employees><employee><emp_id>1</emp_id><emp_name>Bob</emp_name><skills> <skills_id>1</skills_id> <skills_id>tsql</skills_id> <skills_id>2</skills_id> <skills_id>SSRS</skills_id> <skills_id>3</skills_id> <skills_id>SSAS</skills_id> <skills_id>4</skills_id> <skills_id>SSIS</skills_id> <skills_id>5</skills_id> <skills_id>Replication</skills_id></skills></employee><employee><emp_id>2</emp_id><emp_name>Frank</emp_name><skills> <skills_id>1</skills_id> <skills_id>tsql</skills_id> <skills_id>2</skills_id> <skills_id>SSRS</skills_id> <skills_id>3</skills_id> <skills_id>SSAS</skills_id></skills></employee></employees>'SELECT e.c.value('(emp_id)[1]', 'int') AS emp_id , e.c.value('(emp_name/text())[1]', 'varchar(50)') AS emp_name , s.c.value('(.)[1]', 'varchar(50)') AS skills_idFROM @xml.nodes('/employees/employee') e(c)CROSS APPLY e.c.nodes('skills/skills_id') AS s(c)[/code]Not sure if it is going to be what you are after though looking at that data. Did you mean to have the skills id and the skill name in xml nodes of the same name? as by looking at the data i'm guessing you may would like to have it so the skills id is in a column and the skill name is also in a column and both related to each other rather than both in a single column.Fri, 01 Mar 2013 07:14:53 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1425492-21-1.aspxHi, looking for tSQL help on shredding the following xml so that I can see (in this example) all skills per employee[code="xml"]'<employees><employee><emp_id>1</emp_id><emp_name>Bob</emp_name><skills> <skills_id>1</skills_id> <skills_name>tsql</skills_id> <skills_id>2</skills_id> <skills_name>SSRS</skills_id> <skills_id>3</skills_id> <skills_name>SSAS</skills_id> <skills_id>4</skills_id> <skills_name>SSIS</skills_id> <skills_id>5</skills_id> <skills_name>Replication</skills_id></skills></employee><employee><emp_id>2</emp_id><emp_name>Frank</emp_name><skills> <skills_id>1</skills_id> <skills_name>tsql</skills_id> <skills_id>2</skills_id> <skills_name>SSRS</skills_id> <skills_id>3</skills_id> <skills_name>SSAS</skills_id></skills></employee></employees>'[/code]Thanks*edited to show skill_id AND skill_name (not just skill_id)Fri, 01 Mar 2013 07:02:53 GMTNickBalaam