Retrieve XML sql field into standard SQL result set

• Neil-222145

  Old Hand

  Points: 347

  More actions

  November 19, 2007 at 8:16 am

  #76823

  I have tried looking at openxml but none of the examples seem to be relevant for this format of xml.

  I have a table with an XML field that contains data formatted as below.

  I need to be able to query this so that have a result set like this

  Report User Joe.Bloggs

  Report User John.Smith

  Content Manager Jane.Brown

  TIA

  Neil.

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  November 19, 2007 at 8:26 am

  #752312

  Could you please repost your question and escape the < with & lt;

  XML can get filtered out from posts if they are not escaped 🙁

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Neil-222145

  Old Hand

  Points: 347

  More actions

  November 19, 2007 at 8:58 am

  #752334

  I hope this formats ok, I've replaced brackets with &lt and &gt

  &ltPolicies&gt

  &ltPolicy&gt

  &ltGroupUserName&gtJoe.Bloggs&lt/GroupUserName&gt

  &ltRoles&gt

  &ltRole&gt

  &ltName&gtReport User&lt/Name&gt

  &lt/Role&gt

  &lt/Roles&gt

  &lt/Policy&gt

  &ltPolicy&gt

  &ltGroupUserName&gtJohn.Smith&lt/GroupUserName&gt

  &ltRoles&gt

  &ltRole&gt

  &ltName&gtReport User&lt/Name&gt

  &lt/Role&gt

  &lt/Roles&gt

  &lt/Policy&gt

  &ltPolicy&gt

  &ltGroupUserName&gtJane.Brown&lt/GroupUserName&gt

  &ltRoles&gt

  &ltRole&gt

  &ltName&gtContent Manager&lt/Name&gt

  &lt/Role&gt

  &lt/Roles&gt

  &lt/Policy&gt

  &lt/Policies&gt

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  November 19, 2007 at 9:21 am

  #752354

  A simple solution (may not be the best though):

  SELECT a.Policy.query('data(./Roles/Role/Name)')

  , a.Policy.query('data(./GroupUserName)')

  FROM xmlTable CROSS APPLY x.nodes('//Policy') AS a(Policy)

  To set it up (for others who would like to play):

  create table xmlTable (x xml)

  insert into xmlTable values (

  N'<Policies>

  <Policy>

  <GroupUserName>Joe.Bloggs</GroupUserName><Roles>

  <Role>

  <Name>Report User</Name>

  </Role>

  </Roles>

  </Policy>

  <Policy>

  <GroupUserName>John.Smith</GroupUserName>

  <Roles>

  <Role>

  <Name>Report User</Name>

  </Role>

  </Roles>

  </Policy>

  <Policy>

  <GroupUserName>Jane.Brown</GroupUserName>

  <Roles>

  <Role>

  <Name>Content Manager</Name>

  </Role>

  </Roles>

  </Policy>

  </Policies>')

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Neil-222145

  Old Hand

  Points: 347

  More actions

  November 19, 2007 at 9:37 am

  #752367

  Thanks for the quick help Andras, but I've made a mistake, the data which is XML formatted is actually held in a ntext data type.

  The actual need for this is that we have many SSRS reports deployed and there does not seem to be an easy way to see permissions from a user/group perspective, as you have to go into each report to see who has what permission rather than Joe Bloggs can run these X reports. I've traced the content to the xmldescription field on the secdata table on the ReportServer database and that contains the xml data.

  Thanks

  Neil.