Trouble Querying XML

• grecci1077

  SSC Enthusiast

  Points: 149

  More actions

  September 27, 2012 at 12:50 pm

  #268313

  This is what I am working with:

  DECLARE @myDoc xml

  DECLARE @ProdID int

  SET @myDoc = '<?xml version="1.0" encoding="UTF-8"?>

  <IB>

  <appname>Microsoft</appname>

  <membership>

  <String>IB\Greg</String>

  <String>IB\Carla</String>

  <String>IB\Peter</String>

  </membership>

  <appname>Oracle</appname>

  <membership>

  <String>IB\Blin</String>

  <String>IB\Larry</String>

  <String>IB\John</String>

  </membership>

  <appname>Google</appname>

  <membership>

  <String>IB\Kim</String>

  <String>IB\Alain</String>

  <String>IB\Danny</String>

  </membership>

  </IB>

  '

  select x.[desc].value('.','varchar (230)') as [desc], y.[desc].value('.','varchar (230)') as [path]

  from @mydoc.nodes('/IB/appname') x ([desc])

  outer apply x.[desc].nodes('/IB/membership') y ([desc])

  MicrosoftIB\GregIB\CarlaIB\Peter

  MicrosoftIB\BlinIB\LarryIB\John

  MicrosoftIB\KimIB\AlainIB\Danny

  OracleIB\GregIB\CarlaIB\Peter

  OracleIB\BlinIB\LarryIB\John

  OracleIB\KimIB\AlainIB\Danny

  GoogleIB\GregIB\CarlaIB\Peter

  GoogleIB\BlinIB\LarryIB\John

  GoogleIB\KimIB\AlainIB\Danny

  But Ideally, I would like to get:

  Microsoft | IB\greg,IB\Carla, IB\Peter

  Oracle | IB\Blin,IB\Larry,IB\John

  Google | IB\Kim,IB\Alain,IB\Danny

  What am I missing here? Thanks.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 27, 2012 at 1:35 pm

  #1543077

  You might get away with this, but to be truthful, I haven't checked whether it would always guarantee the correct order/matching.

  The xml is not easily handled in T-SQL because of the lack of sibling handling.

  select [desc],stuff([path].value('(./text())[1]','varchar(230)'),1,1,'') as [path]

  from (

  select x.[desc].value('.','varchar (230)') as [desc],ROW_NUMBER() OVER(ORDER BY @@SPID) as rn

  from @mydoc.nodes('/IB/appname') x ([desc])

  ) nd1

  , (

  select y.[desc].query('for $s in ./String return concat(",",$s)') as [path],ROW_NUMBER() OVER(ORDER BY @@SPID) as rn

  from @mydoc.nodes('/IB/membership') y ([desc])

  ) nd2

  where nd1.rn = nd2.rn

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]