Help me with for Xml path()

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 7:09 am

  #327432

  Hi all, 

  drop table #temp
drop table #statistics

  create table #temp ( ID int, [TYPE] varchar(100), BATCH_NO int, Xml_name varchar(50))

  create table #statistics ( ID int, name varchar(10) , bank varchar(50 ))

  insert into #statistics (ID,name,bank)
select 1,'AAA','HSBC'
union all
select 2,'BBB','JPMORGON'
union all
 select 3,'CCC','CITIGROUP'
union all
  select 4,'DDD','ROYALCANADA'

      insert into #temp(ID,[TYPE],BATCH_NO,Xml_name)
     select 1,'BANKGROUP',1,'XML_1_2'
union all
     select 2,'BANKGROUP',1,'XML_1_2'
union all
     select 3,'BANKGROUP',1,'XML_3_4'
union all
      select 4,'BANKGROUP',1,'XML_3_4'
  
      I am able to bring xml out from this as a whole,
  select (
  select ID,
  name,
  bank
  from #statistics b where a.ID = b.ID
  for xml path (''),root('STATICTICS'),type)

    from #temp a
  for xml path(''),type,root('Bank')
  

  but i want to split this xmlout  along with xml_name      

  i want below output :

  ID	XML_out	Xml_name
  1,2	<<1>,<2>>	XML_1_2
  3,4	<<3>,<4>>	XML_3_4

  Note : <<1>,<2>> is just to show that it has 1 & 2 information in xml format) 
  if i click that XML_out , below should come

  <Bank>
  <STATICTICS>
    <ID>1</ID>
    <name>AAA</name>
    <bank>HSBC</bank>
  </STATICTICS>
  <STATICTICS>
    <ID>2</ID>
    <name>BBB</name>
    <bank>JPMORGON</bank>
  </STATICTICS>
  </Bank>

  and below for 2 nd xml out

  <Bank>
  <STATICTICS>
    <ID>3</ID>
    <name>CCC</name>
    <bank>CITIGROUP</bank>
  </STATICTICS>
  <STATICTICS>
    <ID>4</ID>
    <name>DDD</name>
    <bank>ROYALCANADA</bank>
  </STATICTICS>
  </Bank>

  Hope expert can do it... i have not done for xml before .Please help me .. thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  February 15, 2018 at 7:54 am

  #1979906

  JoNTSQLSrv - Thursday, February 15, 2018 7:09 AM

  Hi all, 

  drop table #temp
drop table #statistics

  create table #temp ( ID int, [TYPE] varchar(100), BATCH_NO int, Xml_name varchar(50))

  create table #statistics ( ID int, name varchar(10) , bank varchar(50 ))

  insert into #statistics (ID,name,bank)
select 1,'AAA','HSBC'
union all
select 2,'BBB','JPMORGON'
union all
 select 3,'CCC','CITIGROUP'
union all
  select 4,'DDD','ROYALCANADA'

      insert into #temp(ID,[TYPE],BATCH_NO,Xml_name)
     select 1,'BANKGROUP',1,'XML_1_2'
union all
     select 2,'BANKGROUP',1,'XML_1_2'
union all
     select 3,'BANKGROUP',1,'XML_3_4'
union all
      select 4,'BANKGROUP',1,'XML_3_4'
  
      I am able to bring xml out from this as a whole,
  select (
     select ID,
     name,
     bank
     from #statistics b where a.ID = b.ID
     for xml path (''),root('STATICTICS'),type)

       from #temp a
     for xml path(''),type,root('Bank')
from #temp a     for xml path(''),type,root('Bank')  

  but i want to split this xmlout  along with xml_name      

  i want below output :

  ID	XML_out	Xml_name
  1,2	<<1>,<2>>	XML_1_2
  3,4	<<3>,<4>>	XML_3_4

  Note : <<1>,<2>> is just to show that it has 1 & 2 information in xml format) 
  if i click that XML_out , below should come

  <Bank>
  <STATICTICS>
    <ID>1</ID>
    <name>AAA</name>
    <bank>HSBC</bank>
  </STATICTICS>
  <STATICTICS>
    <ID>2</ID>
    <name>BBB</name>
    <bank>JPMORGON</bank>
  </STATICTICS>
  </Bank>

  and below for 2 nd xml out

  <Bank>
  <STATICTICS>
    <ID>3</ID>
    <name>CCC</name>
    <bank>CITIGROUP</bank>
  </STATICTICS>
  <STATICTICS>
    <ID>4</ID>
    <name>DDD</name>
    <bank>ROYALCANADA</bank>
  </STATICTICS>
  </Bank>

  Hope expert can do it... i have not done for xml before .Please help me .. thanks

  Quick question, can you post the exact desired output please?
  😎

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 9:19 am

  #1979935

  Eirikur Eiriksson - Thursday, February 15, 2018 7:54 AM

  Quick question, can you post the exact desired output please?
  😎

  HI Eirikur Eiriksson sql GURU, 

    I am able to bring xml out from this as a whole,
  select (
     select ID,
     name,
     bank
     from #statistics b where a.ID = b.ID 
     for xml path (''),root('STATICTICS'),type)

    

  check above image I am getting whole records as xml output 

  But i want like below 

  ID	XML_out	Xml_name
  1,2	<<1>,<2>>	XML_1_2
  3,4	<<3>,<4>>	XML_3_4

   check  below image, the out put that i want , 
  

  Please  check select * from #temp .  ID and XML_name

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 15, 2018 at 9:36 am

  #1979938

  What is your defining logic that data for HSBC and JPMORGON be in the same result? Is it because of the column with "XML_1_2" in it? Do you define the relationship anywhere else apart from in a delimited string with prefix?

  Could there be a row with "XML_5_6_7", or even "XML_8_9_10_11_12_13" (more than two XML numbers)?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 15, 2018 at 9:41 am

  #1979940

  You're trying to do everything in one subquery.  You need to break it out into multiple subqueries.

  
WITH XML_Names AS
(
    SELECT DISTINCT Xml_name
    FROM #temp
)
SELECT STUFF( ( SELECT ',', [ID] AS [text()] FROM #temp t WHERE t.Xml_name = xn.Xml_name ORDER BY [ID] FOR XML PATH('')), 1, 1, ''),
    (
        SELECT
        (
            select ID,
             name,
             bank
            from #statistics s where t.ID = s.ID 
            for xml path (''),root('STATICTICS'),type
        )
        from #temp t
        WHERE t.Xml_name = xn.Xml_name
        for xml path(''),type,root('Bank')
    ),
    xn.Xml_name
FROM XML_Names xn

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 15, 2018 at 9:51 am

  #1979943

  There a lot of guesswork on this, but, perhaps...

  WITH Groupings AS(
  SELECT DISTINCT s.ID, t.[TYPE], t.BATCH_NO, t.Xml_name, s.bank, s.[name]
  FROM #temp t
    CROSS APPLY STRING_SPLIT(Xml_name,'_') SS
    JOIN #statistics s ON SS.[value] = s.ID
  WHERE SS.[value] <> 'XML')
SELECT STUFF((SELECT ','+ CONVERT(varchar(6),sq.ID)
     FROM Groupings sq
     WHERE sq.Xml_name = G.Xml_name
     FOR XML PATH('')),1,1,'') AS ID,
   (SELECT sq.ID,
     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;

  Alignment courtesy of SSC.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 9:57 am

  #1979944

  drew.allen - Thursday, February 15, 2018 9:41 AM

  You're trying to do everything in one subquery.  You need to break it out into multiple subqueries.

  
WITH XML_Names AS
(
    SELECT DISTINCT Xml_name
    FROM #temp
)
SELECT STUFF( ( SELECT ',', [ID] AS [text()] FROM #temp t WHERE t.Xml_name = xn.Xml_name ORDER BY [ID] FOR XML PATH('')), 1, 1, ''),
    (
        SELECT
        (
            select ID,
             name,
             bank
            from #statistics s where t.ID = s.ID 
            for xml path (''),root('STATICTICS'),type
        )
        from #temp t
        WHERE t.Xml_name = xn.Xml_name
        for xml path(''),type,root('Bank')
    ),
    xn.Xml_name
FROM XML_Names xn

  Drew

  Wooooow . No words. Awesome. Thanks.

  You're trying to do everything in one subquery. You need to break it out into multiple subqueries. 

  Yes you are right.  I am  confused Since this xml out is new to me

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 10:03 am

  #1979947

  Thom A - Thursday, February 15, 2018 9:36 AM

  What is your defining logic that data for HSBC and JPMORGON be in the same result? Is it because of the column with "XML_1_2" in it? Do you define the relationship anywhere else apart from in a delimited string with prefix?

  Could there be a row with "XML_5_6_7", or even "XML_8_9_10_11_12_13" (more than two XML numbers)?

  Hi Thom A, 

   Actually it is like ID 1 and 2 into 1 xml_name so xml name is starting ID and ending ID (Xml_1_2) ,  and ID 3 and 4 to Xml_3_4 and ID 5 and 6 to xml_5_6

  It is combining 2 ID info into 1  xml . 

  if it is 4 different  id in 1 xml means XML name whould be XML_1_4 , XML_5_8 ext

  Hope you got it

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 15, 2018 at 10:07 am

  #1979948

  JoNTSQLSrv - Thursday, February 15, 2018 10:03 AM

  Hi Thom A, 

   Actually it is like ID 1 and 2 into 1 xml_name so xml name is starting ID and ending ID (Xml_1_2) ,  and ID 3 and 4 to Xml_3_4 and ID 5 and 6 to xml_5_6

  It is combining 2 ID info into 1  xml . 

  if it is 4 different  id in 1 xml means XML name whould be XML_1_4 , XML_5_8 ext

  Hope you got it

  Wait, so XML_1_4, means ID's 1, 2, 3, and 4 should be picked up? Neither my, nor Drew's will work for that (infact, changing XML_1_2 to XML_1_3 doesn't change Drew's results at all).

  If this is the case, this is a "little" more involved.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 10:14 am

  #1979952

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

  JoNTSQLSrv - Thursday, February 15, 2018 10:03 AM

  Hi Thom A, 

   Actually it is like ID 1 and 2 into 1 xml_name so xml name is starting ID and ending ID (Xml_1_2) ,  and ID 3 and 4 to Xml_3_4 and ID 5 and 6 to xml_5_6

  It is combining 2 ID info into 1  xml . 

  if it is 4 different  id in 1 xml means XML name whould be XML_1_4 , XML_5_8 ext

  Hope you got it

  Wait, so XML_1_4, means ID's 1, 2, 3, and 4 should be picked up? Neither my, nor Drew's will work for that (infact, changing XML_1_2 to XML_1_3 doesn't change Drew's results at all).

  If this is the case, this is a "little" more involved.

  Actually , no need to bother about the ID and xml_name  in table  #temp, it is already given from flat file , we need to just combine or group the  id info to xml format with  given   xml_name. that s it

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 15, 2018 at 10:18 am

  #1979954

  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;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 15, 2018 at 11:41 am

  #1979971

  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;

  Hi Thom A ,

   I am getting below error 

  Msg 208, Level 16, State 1, Line 2
  Invalid object name 'STRING_SPLIT'.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 15, 2018 at 12:28 pm

  #1979978

  JoNTSQLSrv - Thursday, February 15, 2018 11:41 AM

  Hi Thom A ,

   I am getting below error 

  Msg 208, Level 16, State 1, Line 2
  Invalid object name 'STRING_SPLIT'.

  You've posted in a SQL Server 2016 forum, and STRING_SPLIT was introduced with that version.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 15, 2018 at 12:40 pm

  #1979981

  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

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• JoNTSQLSrv

  Default port

  Points: 1420

  More actions

  February 17, 2018 at 3:20 am

  #1980160

  Thom A - Thursday, February 15, 2018 12:28 PM

  JoNTSQLSrv - Thursday, February 15, 2018 11:41 AM

  Hi Thom A ,

   I am getting below error 

  Msg 208, Level 16, State 1, Line 2
  Invalid object name 'STRING_SPLIT'.

  You've posted in a SQL Server 2016 forum, and STRING_SPLIT was introduced with that version.

  Ohh sorry mine is 2012. My home i have 2016 but in office 2012.