January 23, 2020 at 6:51 pm
Hi
I have the XML field below I need to concatenate the "Purpose of Request" field if the 'Checked' value = 'true'
So in my example the output would be 'New Bill, Additional Spending money/Leisure'
Thanks
<Data>
<Date_x0020_Required>2020-01-16T00:00:00-05:00</Date_x0020_Required>
<Check_x0020_Amount>10000</Check_x0020_Amount>
<Check_x0020_Payable_x0020_to_x0020_Name>Chie</Check_x0020_Payable_x0020_to_x0020_Name>
<Check_x0020_Payable_x0020_to_x0020_Address>1 ian st anytone/Check_x0020_Payable_x0020_to_x0020_Address>
<Sending_x0020_Options>Mail to above address</Sending_x0020_Options>
<Type>
<Data_Type_Option>
<Name>Special</Name>
<Checked>true</Checked>
</Data_Type_Option>
<Data_Type_Option>
<Name>Emergency</Name>
<Checked>false</Checked>
</Data_Type_Option>
</Type>
<Purpose_x0020_of_x0020_Request>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>New Bill</Name>
<Checked>true</Checked>
<Text>Test</Text>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Bill Overages</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Housing Related Need</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Clothing</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Additional Spending money/Leisure</Name>
<Checked>true</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Food</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Medications</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
<Data_Purpose_x0020_of_x0020_Request_Option>
<Name>Other Unanticipated Expense</Name>
<Checked>false</Checked>
</Data_Purpose_x0020_of_x0020_Request_Option>
</Purpose_x0020_of_x0020_Request>
</Data>
January 23, 2020 at 7:35 pm
Can you post the desired results please?
😎
January 23, 2020 at 8:00 pm
Hi Eirikur,
Thanks for getting back
So in the example , the output for "Purpose of Request" would be " 'New Bill, Additional Spending money/Leisure' since only those two are checked=true.
But all of the choices could be checked in other records
Thanks
Joe
January 23, 2020 at 11:15 pm
I used an XML variable, but you can easily replace the variable with your field.
SELECT STUFF(
(
SELECT ',', c.value('.', 'VARCHAR(20)')
FROM @doc.nodes('/Data/Purpose_x0020_of_x0020_Request/Data_Purpose_x0020_of_x0020_Request_Option[Checked="true"]/Name') T(c)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(255)'), 1, 1, '');
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 24, 2020 at 2:58 pm
Thanks Drew,
Works perfectly , I'm sorry, I forgot to add the there is a "Text" field if Checked=true.
I tried to add it in , but not getting the output I expected.
In the XML if checked =true then there is a "Text" field and I would like to add it to the output
So in the example the output would be New Bill (Test),Bill Overages,Food
In that, New Bill had Test in the Text field but Bill Overages and Food would have nothing since there was no 'Text'
Thanks Again
Joe
January 24, 2020 at 3:19 pm
Show us what you've tried and we'll help you through it. There are a couple of ways to approach it. I would probably start by changing what nodes are returned by the nodes() function. The rest should be relatively easy from there.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 28, 2020 at 7:09 pm
Hi
Sorry, i got caught up doing another project....
Honestly, I "sorta" get what's going on here,
STUFF(
(
SELECT ',',
c.value('.', 'VARCHAR(20)')
FROM data.nodes('/Data/Purpose_x0020_of_x0020_Request/Data_Purpose_x0020_of_x0020_Request_Option[Checked="true"]/Name') T(c)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(255)'), 1, 1, ''
) as PurposeOfRequest
parsing out the name , I tried using the same method with a concat , but wasn't even close
tried removing "/name but get the <Checked> value
My end result (from the original XML above) would look something like
New Bill (Test),Bill Overages (bill over),Food (food)
The new values in the output would be the value in <Text>
Thanks
January 29, 2020 at 4:56 pm
Hi
Sorry, i got caught up doing another project....
Honestly, I "sorta" get what's going on here,
STUFF(
(
SELECT ',',
c.value('.', 'VARCHAR(20)')
FROM data.nodes('/Data/Purpose_x0020_of_x0020_Request/Data_Purpose_x0020_of_x0020_Request_Option[Checked="true"]/Name') T(c)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(255)'), 1, 1, ''
) as PurposeOfRequestparsing out the name , I tried using the same method with a concat , but wasn't even close
tried removing "/name but get the <Checked> value
My end result (from the original XML above) would look something like
New Bill (Test),Bill Overages (bill over),Food (food)
The new values in the output would be the value in <Text>
Thanks
There is no way to get that output from your sample data, because (1) Bill Overages and Food are FALSE, so they wouldn't be included and (2) Bill Overages and Food do not contain a <Text> node.
You have to remember that the path used in the c.value() function is relative to each node produce by the .nodes() function. If you change the path in one and do not change the path in the other, the full paths will be different, so the final values will be different.
SELECT STUFF(
(
SELECT ',', c.value('Name[1]', 'VARCHAR(20)') + COALESCE('('+c.value('Text[1]', 'VARCHAR(20)')+')', '')
FROM @doc.nodes('/Data/Purpose_x0020_of_x0020_Request/Data_Purpose_x0020_of_x0020_Request_Option[Checked="true"]') T(c)
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(255)'), 1, 1, '');
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply