January 23, 2012 at 11:10 am
I've got an xml file with this format :
<Policies>
<Policy>
<GroupUserName>BUILTIN\Administradores</GroupUserName>
<GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId>
<Roles>
<Role><Name>Administrador de contenido</Name></Role>
</Roles>
</Policy>
<Policy>
<GroupUserName>MEMSYS\EMEJIA</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAAVss+Zs3s+svCUho7wgQAAA==</GroupUserId>
<Roles>
<Role><Name>Administrador de contenido</Name></Role>
<Role><Name>Explorador</Name></Role>
<Role><Name>Generador de informes</Name></Role>
<Role><Name>Mis informes</Name></Role>
<Role><Name>Publicador</Name></Role>
</Roles>
</Policy>
</Policies>
and im trying to get the combined info of the <GroupUserName> and <Roles> like this :
GroupUserName || Name
BUILTIN\Administradores || Administrador de contenido, Explorador,Generador de informes
Memsys\EMEJIA || Administrador de contenido, Explorador,Generador de informes
Any ideas of how i could do that with OPENXML??:unsure:
January 23, 2012 at 11:14 am
Any particular reason to use OpenXML instead of XQuery?
I'm not particularly familiar with OpenXML, but XQuery will get those values easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2012 at 12:40 pm
GSquared (1/23/2012)
Any particular reason to use OpenXML instead of XQuery?I'm not particularly familiar with OpenXML, but XQuery will get those values easily.
There´s Not a particular reason to use OpenXML, just trying to find the best approach to do that 😀
January 23, 2012 at 1:34 pm
Something like this?
DECLARE @xml XML
SET @xml='<Policies>
<Policy>
<GroupUserName>BUILTIN\Administradores</GroupUserName>
<GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId>
<Roles>
<Role><Name>Administrador de contenido</Name></Role>
</Roles>
</Policy>
<Policy>
<GroupUserName>MEMSYS\EMEJIA</GroupUserName>
<GroupUserId>AQUAAAAAAAUVAAAAVss+Zs3s+svCUho7wgQAAA==</GroupUserId>
<Roles>
<Role><Name>Administrador de contenido</Name></Role>
<Role><Name>Explorador</Name></Role>
<Role><Name>Generador de informes</Name></Role>
<Role><Name>Mis informes</Name></Role>
<Role><Name>Publicador</Name></Role>
</Roles>
</Policy>
</Policies>'
;
WITH cte AS
(
SELECT
c.value ('(GroupUserName/text())[1]','VARCHAR(255)') GroupUserName,
v.value ('(Name/text())[1]','VARCHAR(255)') Name
FROM @xml.nodes ('Policies/Policy') T(c)
CROSS APPLY c.nodes('Roles/Role') U(v)
)
SELECT
GroupUserName,
STUFF(
(SELECT ', '+ cte1.Name
FROM cte cte1
WHERE cte.GroupUserName = cte1.GroupUserName
FOR XML PATH('')
) ,1,2,'') AS Name
FROM cte
GROUP BY GroupUserName
I assume you're looking for a comma separated list, hence the FOR XML part.
January 23, 2012 at 2:28 pm
Thanks men , it worked like a charm!!!!:-)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply