sjterrell
asked on
Microsoft SQL XML Processing
I have an XML String that is stored in a SQL table as a XML field. I need to be able to extract the following fields using TSQL.
Date
DeviceDate
UserName
FirstName
LastName
ResponseID
Job_Num value (in the Response nodes)
I have tried:
SELECT
x.XmlCol.value('(Response/ Label)[3]' ,'VARCHAR( 100)') as xLable,
x.XmlCol.value('(Response/ Value)[3]' ,'VARCHAR( 100)') as xValue
FROM gcREsponses b
CROSS APPLY b.gcResponse_formXML.nodes ('/CanvasR esult/Subm issions/Su bmission/S ections/Se ction/Scre ens/Screen /Responses ') x(XmlCol)
Which works for that particular form, but I cannot always count on Job_Num being in the 3rd element. It could change based on the form type being submitted.
Any help would be appreciated.
<CanvasResult>
<TotalPages>1</TotalPages>
<CurrentPage>1</CurrentPag e>
<Submissions>
<Submission Id="25934640">
<Form Id="1130317">
<Name>Jobsite Visit Report</Name>
<Status>published</Status>
<Version>17</Version>
</Form>
<Date>2016.08.19 15:33:25</Date>
<DeviceDate>2016.08.19 15:33:21</DeviceDate>
<UserName>tfelker@anthonys ylvan.com< /UserName>
<FirstName>Tyson</FirstNam e>
<LastName>Felker</LastName >
<ResponseID>302192D3-5B2C- 4F9C-945D- FF299FF6EF 81</Respon seID>
<Sections>
<Section>
<Name>Jobsite Check-In</Name>
<Screens>
<Screen>
<Name>Jobsite Check-In</Name>
<Responses>
<Response Guid="A0F4608DFD5B33225F09 E03E5724BB BED79A54D4 ">
<Label>Customer Name</Label>
<Value>Linn</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="644602B41B22A484C079 CA37F5BCE9 F718EBA576 ">
<Label>Project Manager</Label>
<Value>Tyson Felker</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="A3610456E677A9D076CD E2FA6D5E87 03D5BA3E0E ">
<Label>Job_Num</Label>
<Value>825115</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="B5FA40E6998CC41AE025 3C21594C45 A06E3BCC62 ">
<Label>Type of Visit</Label>
<Value>Jobsite Visit</Value>
<Type>Value List</Type>
</Response>
<Response Guid="670EA728025BD292E1E4 C51219A273 151401C6E0 ">
<Label>Notes From Visit</Label>
<Value>Discussed redesign</Value>
<Type>Multi-Line Text</Type>
</Response>
<Response Guid="C3B98287ECD03F2A44AA C636079C2F FBFA613243 ">
<Label>Picture 1</Label>
<Value>1733636793</Value>
<Type>Image Capture</Type>
</Response>
<Response Guid="EEB69B0F77A230FDF766 7EC1396684 1E4DBA3C51 ">
<Label>Picture 2</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="943560DFD0FFA5961863 C872DD1B15 5054B8D5CE ">
<Label>Picture 3</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="387F6AB8EEA60A6F018C 20FE5A516F B8A392944B ">
<Label>Picture 4</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="CA2B6DB96D593C541A05 7FC3FD5ECD 8343DDC058 ">
<Label>Picture 5</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="74CCDE93909A462397A9 8ECE9EE367 121D8A2040 ">
<Label>Picture 6</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="BB8D608210C4D9FD4A9F 45959B556A 759B6FA248 ">
<Label>GPS </Label>
<Value>Lat:30.278769,Lon:- 97.846046, Acc:2277.6 91650,Alt: 236.837006 ,Bear:-1.0 00000,Spee d:-1.00000 0,Time:Fri , 19 Aug 2016 10:32:46 -0500</Value>
<Type>GPS</Type>
</Response>
<Response Guid="4C02BDC6B1E9090A7416 98F8CFB652 5AF1CAADB2 ">
<Label>Date</Label>
<Value>08/19/2016</Value>
<Type>Date</Type>
</Response>
<Response Guid="B0FC3C7160BFDEB372CA 5C516884FE A19AC903B4 ">
<Label>Cell_Num</Label>
<Value />
<Type>Text Box</Type>
</Response>
</Responses>
</Screen>
</Screens>
</Section>
</Sections>
</Submission>
</Submissions>
</CanvasResult>
Date
DeviceDate
UserName
FirstName
LastName
ResponseID
Job_Num value (in the Response nodes)
I have tried:
SELECT
x.XmlCol.value('(Response/
x.XmlCol.value('(Response/
FROM gcREsponses b
CROSS APPLY b.gcResponse_formXML.nodes
Which works for that particular form, but I cannot always count on Job_Num being in the 3rd element. It could change based on the form type being submitted.
Any help would be appreciated.
<CanvasResult>
<TotalPages>1</TotalPages>
<CurrentPage>1</CurrentPag
<Submissions>
<Submission Id="25934640">
<Form Id="1130317">
<Name>Jobsite Visit Report</Name>
<Status>published</Status>
<Version>17</Version>
</Form>
<Date>2016.08.19 15:33:25</Date>
<DeviceDate>2016.08.19 15:33:21</DeviceDate>
<UserName>tfelker@anthonys
<FirstName>Tyson</FirstNam
<LastName>Felker</LastName
<ResponseID>302192D3-5B2C-
<Sections>
<Section>
<Name>Jobsite Check-In</Name>
<Screens>
<Screen>
<Name>Jobsite Check-In</Name>
<Responses>
<Response Guid="A0F4608DFD5B33225F09
<Label>Customer Name</Label>
<Value>Linn</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="644602B41B22A484C079
<Label>Project Manager</Label>
<Value>Tyson Felker</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="A3610456E677A9D076CD
<Label>Job_Num</Label>
<Value>825115</Value>
<Type>Text Box</Type>
</Response>
<Response Guid="B5FA40E6998CC41AE025
<Label>Type of Visit</Label>
<Value>Jobsite Visit</Value>
<Type>Value List</Type>
</Response>
<Response Guid="670EA728025BD292E1E4
<Label>Notes From Visit</Label>
<Value>Discussed redesign</Value>
<Type>Multi-Line Text</Type>
</Response>
<Response Guid="C3B98287ECD03F2A44AA
<Label>Picture 1</Label>
<Value>1733636793</Value>
<Type>Image Capture</Type>
</Response>
<Response Guid="EEB69B0F77A230FDF766
<Label>Picture 2</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="943560DFD0FFA5961863
<Label>Picture 3</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="387F6AB8EEA60A6F018C
<Label>Picture 4</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="CA2B6DB96D593C541A05
<Label>Picture 5</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="74CCDE93909A462397A9
<Label>Picture 6</Label>
<Value />
<Type>Image Capture</Type>
</Response>
<Response Guid="BB8D608210C4D9FD4A9F
<Label>GPS </Label>
<Value>Lat:30.278769,Lon:-
<Type>GPS</Type>
</Response>
<Response Guid="4C02BDC6B1E9090A7416
<Label>Date</Label>
<Value>08/19/2016</Value>
<Type>Date</Type>
</Response>
<Response Guid="B0FC3C7160BFDEB372CA
<Label>Cell_Num</Label>
<Value />
<Type>Text Box</Type>
</Response>
</Responses>
</Screen>
</Screens>
</Section>
</Sections>
</Submission>
</Submissions>
</CanvasResult>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.