Help me with for Xml path()

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 17, 2018 at 3:21 am

  #1980161

  drew.allen - Thursday, February 15, 2018 12:40 PM

  Thom A - Thursday, February 15, 2018 10:18 AM

  Little more ugly (well, a lot more), someone might have a better answer, however, this works XML_1_3 logic:

  WITH StartEnd AS(
  SELECT t.[TYPE], t.BATCH_NO, t.Xml_name,
    MIN(SS.[value]) AS XMLStart, MAX(ss.[Value]) AS XMLEnd
  FROM #temp t
    CROSS APPLY STRING_SPLIT(Xml_name,'_') SS
  WHERE SS.[value] <> 'XML'
  GROUP BY t.[TYPE], t.BATCH_NO, t.Xml_name),
Betweens AS(
  SELECT *,
    CONVERT(int,XMLStart) AS IDValue
  FROM StartEnd
  UNION ALL
  SELECT B.[TYPE], B.BATCH_NO, B.Xml_name,
    B.XMLStart, B.XMLEnd,
    B.IDValue + 1 AS IDValue
  FROM Betweens B
  WHERE B.IDValue + 1 <= B.XMLEnd),
Groupings AS(
  SELECT B.*,
    s.bank, s.[name]
  FROM Betweens B
   JOIN #statistics s ON B.IDValue = s.ID)
SELECT STUFF((SELECT ','+ CONVERT(varchar(6),sq.IDValue)
     FROM Groupings sq
     WHERE sq.Xml_name = G.Xml_name
     FOR XML PATH('')),1,1,'') AS ID,
   (SELECT sq.IDValue,
     sq.[name],
     sq.bank
   FROM Groupings sq
   WHERE sq.Xml_name = G.Xml_name
   FOR XML PATH('STATICTICS'), ROOT('Bank'), TYPE), --Is Statictics an intentional typo?
   G.Xml_name
FROM Groupings G
GROUP BY G.Xml_name;

  I'm not sure that this is necessary.  It sounds like the files are provided with the IDs already linked to the XML name and you don't need to reverse engineer the IDs based on the XML name.

  Drew

  yes . Correct