August 31, 2018 at 1:27 pm
I've exhausted everything I have.. I've been trying things for an entire week maybe a week and a half.... I have this code that reads a table which is formatted in XML...
A little sample of the data<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>
There are actually two columns that I need to extract data from and the other is the same format... I have some code that gets the values that I need:
SELECT SUBSTRING(ExtensionSettings, WhereToStarts, WhereToEnds - WhereToStarts - 1) AS Emails from
(select cast(ExtensionSettings as varchar(max)) as ExtensionSettings, charindex( 'TO', cast(ExtensionSettings as varchar(max))) + 16 as WhereToStarts,
charindex( '/Value', cast(ExtensionSettings as varchar(max)) , charindex( 'TO', cast(ExtensionSettings as varchar(max))) + 16) as WhereToEnds
from [Server].[ReportServer].[dbo].[Subscriptions] where report_oid = 'B1C34C2F-5094-4F72-A3CD-397F7BE266C6' and InactiveFlags = 0) t
SELECT SUBSTRING(ExtensionSettings, WhereCCStarts, WhereCCEnds - WhereCCStarts - 1) AS CC FROM
(select cast(ExtensionSettings as varchar(max)) as ExtensionSettings, charindex( 'CC', cast(ExtensionSettings as varchar(max))) + 16 as WhereCCStarts,
charindex( '/Value', cast(ExtensionSettings as varchar(max)) , charindex( 'CC', cast(ExtensionSettings as varchar(max))) + 16) as WhereCCEnds
from [Server].[ReportServer].[dbo].[Subscriptions] where report_oid = 'B1C34C2F-5094-4F72-A3CD-397F7BE266C6' and InactiveFlags = 0) t1
SELECT SUBSTRING(ExtensionSettings, WhereRenderFormatStarts, WhereRenderFormatEnds - WhereRenderFormatStarts - 1) AS RenderFormat from
(select cast(ExtensionSettings as varchar(max)) as ExtensionSettings, charindex( 'RenderFormat', cast(ExtensionSettings as varchar(max))) + 26 as WhereRenderFormatStarts,
charindex( '/Value', cast(ExtensionSettings as varchar(max)) , charindex( 'RenderFormat', cast(ExtensionSettings as varchar(max))) + 26) as WhereRenderFormatEnds
from [Server.[ReportServer].[dbo].[Subscriptions] where report_oid = 'B1C34C2F-5094-4F72-A3CD-397F7BE266C6' and InactiveFlags = 0) t2
SELECT SUBSTRING(Parameters, WhereAKDunsNumberStarts , WhereAKDunsNumberEnds - WhereAKDunsNumberStarts - 1) as AKDunsNumber FROM
(select cast(parameters as varchar(max)) as Parameters, charindex( 'AKDunsNumber', cast(parameters as varchar(max))) + 26 as WhereAKDunsNumberStarts,
charindex( '/Value', cast(Parameters as varchar(max)) , charindex( 'AKDunsNumber', cast(parameters as varchar(max))) + 26) as WhereAKDunsNumberEnds
from [Server].[ReportServer].[dbo].[Subscriptions] where report_oid = 'B1C34C2F-5094-4F72-A3CD-397F7BE266C6' and InactiveFlags = 0) t3
I also have a #Temp table created with 6 columns. Basically I know how to INSERT INTO and everything, but as for these 4 select queries how do I do anything with them? Right now they only show up as 4 separate Rows/ Columns.
If anyone has any ideas please let me know even if it's pointing in the right direction. SQL is not my prominent language.. as I I know just enough to get by and play the role of a "Pretend DBA"
August 31, 2018 at 1:45 pm
If the data is formatted in XML why aren't you using xquery?
You also want to look into using a table value constructor. If you post sample data and expected results (as outlined in the first link in my signature) people will be more likely to help.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 31, 2018 at 1:59 pm
Alright... Here is some same data:
<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>
From this I'm extracting the following:
AKDunsNumber
146159
119529
241828
280644
39156
296293
177030
119529
39156
296590
291542
6249668
225854
296590
241109
146159
280644
245332
228833
291542
225854
295832
241109
296608
296608
296624
295832
296293
273854
225854
296624
5441688
5441688
273854
241828
6249668
228833
245332
The other data which has same layout has more I'm extracting but I cannot post that due to sensitive information.
August 31, 2018 at 2:30 pm
Well you didn't set up your sample data in a table, so I did that for you. Also, there is no way that you are deriving those expected results from that sample data, because that sample data only contains ONE AKDunsNumber, and there is no way that you will get multiple numbers from that sample.
DECLARE @sample TABLE (field XML)
INSERT @sample
VALUES('<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>')
SELECT c.value('Value[1]', 'INT') AS AKDunsNumber
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues/ParameterValue[Name="AKDunsNumber"]') T(c)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2018 at 6:03 am
Yes I do understand that the sample data only had one value. for AKDunsNumber. There are a total of 38 records I need to go through and a total of 4 rows I need to collect data from. Let me try to explain a little better...
I have 38 rows of data I need to collect for and within those 38 rows I have 4 columns I need to collect for:
Col 1 = Emailrecipent
Col 2 = EmailCopy
Col 3 = RenderFormat
Col 4 = AKDunsNumber
What I'm trying to do is create a temp table to store the values in there until it goes through all 38 rows then copy things from the temp table to my destination table. Hopefully that makes a little more sense...
The first select queries I posted get the data but as separate queries.. as in
col1 -> 38 rows
col2 -> 38 rows
col3 -> 38 rows
col4 -> 38 rows
Which gives me 152 total rows.
I'm trying to do this:
col1, col2, col3, col4 -> 38 Rows -> then to destination table.
September 4, 2018 at 6:16 am
don.hughesjr - Tuesday, September 4, 2018 6:03 AMYes I do understand that the sample data only had one value. for AKDunsNumber. There are a total of 38 records I need to go through and a total of 4 rows I need to collect data from. Let me try to explain a little better...I have 38 rows of data I need to collect for and within those 38 rows I have 4 columns I need to collect for:
Col 1 = Emailrecipent
Col 2 = EmailCopy
Col 3 = RenderFormat
Col 4 = AKDunsNumberWhat I'm trying to do is create a temp table to store the values in there until it goes through all 38 rows then copy things from the temp table to my destination table. Hopefully that makes a little more sense...
The first select queries I posted get the data but as separate queries.. as in
col1 -> 38 rows
col2 -> 38 rows
col3 -> 38 rows
col4 -> 38 rowsWhich gives me 152 total rows.
I'm trying to do this:
col1, col2, col3, col4 -> 38 Rows -> then to destination table.
Will this help?DECLARE @sample TABLE (field XML)
INSERT @sample
VALUES('<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>')
SELECT c.value('Name[1]', 'varchar(50)') AS ParameterName, c.value('Value[1]', 'varchar(50)') AS ParameterValue
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues/ParameterValue') T(c)
_____________
Code for TallyGenerator
September 4, 2018 at 6:51 am
That was close. I will need to see if I can tweak it a little to make it 100% like I need. Thanks.
Sadly I'm trying to tweak it a little and this one is basically going through every parameter and I just need 4 of them...
September 4, 2018 at 7:54 am
Like this?
DECLARE @sample TABLE (field XML)
INSERT @sample
VALUES('<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>')
SELECT c.value('Value[1]', 'int') AS AKDunsNumber, d.value('Value[1]', 'varchar(50)') AS prmExpandState
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues/ParameterValue[Name="AKDunsNumber"]') T(c)
OUTER APPLY smpl.field.nodes('/ParameterValues/ParameterValue[Name="prmExpandState"]') T1(d)
_____________
Code for TallyGenerator
September 4, 2018 at 8:16 am
don.hughesjr - Tuesday, September 4, 2018 6:03 AMYes I do understand that the sample data only had one value. for AKDunsNumber. There are a total of 38 records I need to go through and a total of 4 rows I need to collect data from. Let me try to explain a little better...I have 38 rows of data I need to collect for and within those 38 rows I have 4 columns I need to collect for:
Col 1 = Emailrecipent
Col 2 = EmailCopy
Col 3 = RenderFormat
Col 4 = AKDunsNumberWhat I'm trying to do is create a temp table to store the values in there until it goes through all 38 rows then copy things from the temp table to my destination table. Hopefully that makes a little more sense...
The first select queries I posted get the data but as separate queries.. as in
col1 -> 38 rows
col2 -> 38 rows
col3 -> 38 rows
col4 -> 38 rowsWhich gives me 152 total rows.
I'm trying to do this:
col1, col2, col3, col4 -> 38 Rows -> then to destination table.
The solution is only as good as the sample data you provide. You are looking for Email, CC, RenderFormat, and AKDunsNumber, but your sample data only contains AKDunsNumber, so the proposed solution can only give you AKDunsNumber. If you want a better solution, provide better sample data.
The other thing is that the expected results should MATCH the sample data. This is so that people can run your sample data against their code and match their results to your expected results. The closer that their results match the expected results, the more confident they can be that their code does what you are asking. If your sample data has little to no relation to the expected results, it makes it almost impossible to do this comparison.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2018 at 8:39 am
Holy crap thank you so much. I was able to get it to work correctly. I need to narrow the search down. Thank you so much!!
September 4, 2018 at 9:18 am
Sergiy - Tuesday, September 4, 2018 7:54 AMLike this?
DECLARE @sample TABLE (field XML)
INSERT @sample
VALUES('<ParameterValues><ParameterValue><Name>prmExpandState</Name><Value>Collapse</Value></ParameterValue><ParameterValue><Name>AKDunsNumber</Name><Value>146159</Value></ParameterValue></ParameterValues>')SELECT c.value('Value[1]', 'int') AS AKDunsNumber, d.value('Value[1]', 'varchar(50)') AS prmExpandState
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues/ParameterValue[Name="AKDunsNumber"]') T(c)
OUTER APPLY smpl.field.nodes('/ParameterValues/ParameterValue[Name="prmExpandState"]') T1(d)
SELECT c.value('(ParameterValue[Name="AKDunsNumber"]/Value/text())[1]', 'int') AS AKDunsNumber,
c.value('(ParameterValue[Name="prmExpandState"]/Value/text())[1]', 'varchar(50)') AS PrmExpandState
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues') T(c)
This also emphasizes the need for completeness especially when working with XML documents. There are so many ways that you can split the path between the nodes function and the value/query function, that it's impossible to know which will be the most efficient with only partial data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2018 at 10:42 am
Drew,
I did try it your way and I'm getting an error...
XQuery [@sample.field.value()]: The XQuery syntax '/function()' is not supported.
September 4, 2018 at 11:52 am
don.hughesjr - Tuesday, September 4, 2018 10:42 AMDrew,I did try it your way and I'm getting an error...
XQuery [@sample.field.value()]: The XQuery syntax '/function()' is not supported.
Again, we come back to completeness. You say that you tried my way, and that you got an error, but you never provided the query that produced the error. The method works, because I was able to run it, so that means that you didn't implement it correctly, but, since you did not provide your query, we can't tell you where you went wrong in implementing it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2018 at 12:27 pm
The query I used was the following
SELECT c.value('(ParameterValue[Name ="TO"]/Value/Text())[1]', 'nvarchar(max)') AS Emailreceipt, c.value('(ParameterValue[Name ="CC"]/Value/Text())[1]', 'nvarchar(max)') AS EmailCopy,
c.value('(ParameterValue[Name ="RenderFormat"]/Value/Text())[1]', 'nchar(3)') AS RenderFormat, c.value('(ParameterValue[Name ="AKDunsNumber"]/Value/Text())[1]', 'int') AS AKDunsNumber
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues') T(c)
September 4, 2018 at 12:35 pm
don.hughesjr - Tuesday, September 4, 2018 12:27 PMThe query I used was the followingSELECT c.value('(ParameterValue[Name ="TO"]/Value/Text())[1]', 'nvarchar(max)') AS Emailreceipt, c.value('(ParameterValue[Name ="CC"]/Value/Text())[1]', 'nvarchar(max)') AS EmailCopy,
c.value('(ParameterValue[Name ="RenderFormat"]/Value/Text())[1]', 'nchar(3)') AS RenderFormat, c.value('(ParameterValue[Name ="AKDunsNumber"]/Value/Text())[1]', 'int') AS AKDunsNumber
FROM @sample AS smpl
CROSS APPLY smpl.field.nodes('/ParameterValues') T(c)
XML is case-sensitive. The function text() is all lowercase.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply