Need help on XML Query, Some tweaking help needed. - Report Sever related

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  April 19, 2017 at 12:18 pm

  #342526

  The following works, but how do I further breakdown and get what is in the NAME VALUE parts ( See the  query output I have posted below ). What I need then is how do I get the value associated with the <name>TO</name> part ?

  
SELECT
CAST( ExtensionSettings as XML ).query('/ParameterValues/ParameterValue')
  AS ExtensionSettings
FROM
dbo.Subscriptions AS SUB
  INNER JOIN dbo.[Catalog] AS CAT
   ON SUB.Report_OID = CAT.ItemID

  
<ParameterValue>
<Name>TO</Name>
<Value>pzybach@PainMgmt.com</Value>
</ParameterValue>
<ParameterValue>
<Name>CC</Name>
<Value>pnikula@PainMgmt.com</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>EXCEL</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName was executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeLink</Name>
<Value>False</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue>

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 19, 2017 at 12:33 pm

  #1938914

  What have you tried so far to limit your result set?

  Thom~

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

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  April 19, 2017 at 12:46 pm

  #1938922

  Thom A - Wednesday, April 19, 2017 12:33 PM

  What have you tried so far to limit your result set?

  Only the query that I posted

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 19, 2017 at 1:01 pm

  #1938926

  Ok, well best option would be to treat your XML like a dataset. It has repetitive groups, which makes this easier.

  So, with some similar XML I could do the following:
  USE TestDB;
GO

  CREATE TABLE #XML (xmlfield xml);
GO

  INSERT INTO #XML
VALUES (
'<customer>
  <name>Joe Bloggs</name>
  <address>123 Street</address>
</customer>
<customer>
  <name>Jane Smith</name>
  <address>45Road</address>
</customer>
<customer>
  <name>Jack Brown</name>
  <address>99 Lanet</address>
</customer>'
);
GO

  SELECT d.value('(./name)[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);
GO

  DROP TABLE #XML;
GO
  This returns the dataset:
  
Name                Address
-------------------------------------------------- --------------------------------------------------
Joe Bloggs              123 Street
Jane Smith              45Road
Jack Brown              99 Lanet
  I HUNGER FOR SSC TO FIX IT'S PASTING PROBLEMS!

  You should easily be able to apply the same logic to your XML, and all you need do to limit your results is add a WHERE clause.

  Thom~

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

• DesNorton

  SSC-Insane

  Points: 24279

  More actions

  April 19, 2017 at 1:11 pm

  #1938930

  Thom A - Wednesday, April 19, 2017 1:01 PM

  
SELECT d.value('(./name)[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  A performance tip that I learned from Luis Cazares
  
SELECT d.value('(./name/text())[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address/text())[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  ---

  How to post data/code on a forum to get the best help.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  April 19, 2017 at 1:23 pm

  #1938932

  Thom A - Wednesday, April 19, 2017 1:01 PM

  Ok, well best option would be to treat your XML like a dataset. It has repetitive groups, which makes this easier.

  So, with some similar XML I could do the following:
  USE TestDB;
GO

  CREATE TABLE #XML (xmlfield xml);
GO

  INSERT INTO #XML
VALUES (
'<customer>
  <name>Joe Bloggs</name>
  <address>123 Street</address>
</customer>
<customer>
  <name>Jane Smith</name>
  <address>45Road</address>
</customer>
<customer>
  <name>Jack Brown</name>
  <address>99 Lanet</address>
</customer>'
);
GO

  SELECT d.value('(./name)[1]', 'VARCHAR(8000)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(8000)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);
GO

  DROP TABLE #XML;
GO

  You should easily be able to apply the same logic to your XML, and all you need do to limit your results is add a WHERE clause.

  Ok, that worked.. Thx
  

  CREATE TABLE #XML (report_name varchar(500), xmlfield xml);
GO

  INSERT INTO #XML(report_name,xmlfield)
SELECT
CAT.name, SUB.ExtensionSettings

  FROM dbo.Subscriptions AS SUB
  INNER JOIN dbo.[Catalog] AS CAT
   ON SUB.Report_OID = CAT.ItemID
ORDER BY 1

  SELECTx.report_name,
d.value('(./Name)[1]', 'VARCHAR(8000)') AS [Name],
d.value('(./Value)[1]', 'VARCHAR(8000)') AS [Value],

  FROM #XML x
CROSS APPLY xmlfield.nodes('/ParameterValues/ParameterValue') c(d);
GO

  DROP TABLE #XML;
GO

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  April 19, 2017 at 1:27 pm

  #1938933

  Building off Thom's solution, you could do something like this (note that I can't test this as I don't have an SSRS DB available)

  WITH getxml AS
(
  SELECT
    ReportName = CAT.[Name],
    ExtensionSettings =
    CAST(ExtensionSettings as XML).query('/ParameterValues/ParameterValue')
  FROM dbo.Subscriptions AS SUB
  INNER JOIN dbo.[Catalog] AS CAT
  ON SUB.Report_OID = CAT.ItemID
)
SELECT
  ReportName,
  [name]    = nd.value('(name/text())[1]', 'varchar(50)'),
  [address] = nd.value('(address/text())[1]', 'varchar(50)')
FROM getxml
CROSS APPLY ExtensionSettings.nodes('/customer') node(nd);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 19, 2017 at 1:29 pm

  #1938936

  mw112009 - Wednesday, April 19, 2017 1:23 PM

  Thom A - Wednesday, April 19, 2017 1:01 PM

  Ok, well best option would be to treat your XML like a dataset. It has repetitive groups, which makes this easier.

  So, with some similar XML I could do the following:
  USE TestDB;
GO

  CREATE TABLE #XML (xmlfield xml);
GO

  INSERT INTO #XML
VALUES (
'<customer>
  <name>Joe Bloggs</name>
  <address>123 Street</address>
</customer>
<customer>
  <name>Jane Smith</name>
  <address>45Road</address>
</customer>
<customer>
  <name>Jack Brown</name>
  <address>99 Lanet</address>
</customer>'
);
GO

  SELECT d.value('(./name)[1]', 'VARCHAR(8000)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(8000)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);
GO

  DROP TABLE #XML;
GO

  You should easily be able to apply the same logic to your XML, and all you need do to limit your results is add a WHERE clause.

  Ok, that worked.. Thx
  

  CREATE TABLE #XML (report_name varchar(500), xmlfield xml);
GO

  INSERT INTO #XML(report_name,xmlfield)
SELECT
CAT.name, SUB.ExtensionSettings

  FROM dbo.Subscriptions AS SUB
  INNER JOIN dbo.[Catalog] AS CAT
   ON SUB.Report_OID = CAT.ItemID
ORDER BY 1

  SELECTx.report_name,
d.value('(./Name)[1]', 'VARCHAR(8000)') AS [Name],
d.value('(./Value)[1]', 'VARCHAR(8000)') AS [Value],

  FROM #XML x
CROSS APPLY xmlfield.nodes('/ParameterValues/ParameterValue') c(d);
GO

  DROP TABLE #XML;
GO

  Take a look as Des' post. It reduced the Query cost by 50% on my little home machine, so with a big dataset that could work wonders. Thanks for the tip Des, definitely will be updating some of my own queries when I get to the office tomorrow.

  I also updated my post from varchar(8000) to (50) as it was a copy and paste from someothing I'd done at home AGES ago. 🙂 you should use something that represents your data.

  Also, no need to put it into a temporary table first, query it straight from your data. I did that for test data (you should know that, I can't access your tables).

  Edit: Alan beat me to what I was getting at. I don't have an SSRS DB at home either, just SQL Server vNext.

  Thom~

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

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 19, 2017 at 1:32 pm

  #1938937

  DesNorton - Wednesday, April 19, 2017 1:11 PM

  Thom A - Wednesday, April 19, 2017 1:01 PM

  
SELECT d.value('(./name)[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  A performance tip that I learned from Luis Cazares
  
SELECT d.value('(./name/text())[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address/text())[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  Which I learnt from Eirikur Eiriksson. 😉

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 19, 2017 at 1:38 pm

  #1938939

  Luis Cazares - Wednesday, April 19, 2017 1:32 PM

  DesNorton - Wednesday, April 19, 2017 1:11 PM

  Thom A - Wednesday, April 19, 2017 1:01 PM

  
SELECT d.value('(./name)[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address)[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  A performance tip that I learned from Luis Cazares
  
SELECT d.value('(./name/text())[1]', 'VARCHAR(50)') AS [Name],
       d.value('(./address/text())[1]', 'VARCHAR(50)') AS [Address]
FROM #XML x
  CROSS APPLY xmlfield.nodes('/customer') c(d);


  Which I learnt from Eirikur Eiriksson. 😉

  I was honestly (and very pleasantly) surprised at how much that reduced the cost. I was expecting it to be somewhat marginal.

  Thom~

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