Click here to Skip to main content
14,031,218 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I'm connected to SQL DB with Access to collect Survey Response data. All data from a single survey response is contained within a single XML Column from the linked SQL DB view. I'm thinking the best way to extract the node and data is with VBA scripting where I treat the XML Column as a long text string and search for and extract the needed information and write it to another access table. This seems like a long and cumbersome project which is dependent on the XML data string format structure doesn't change, or recoding will have to be done. My other thought is to use a select query on the linked SQL table but the SQL view I've tried doesn't seem to work.

<fields>
<field id="374b4dd2-7729-432d-9ff8-1966c88a08a1" name="email">test
<field id="baf70494-530a-4f22-bc8d-26c4def99cb2" name="contact_customer">No
<field id="f9649c10-aba5-4ff1-8d43-2860dd7c09f7" name="rating">5
<field id="cc7e34b4-e73c-45e2-8f7a-84fa34b36211" name="service_order_number">
<field id="2cdfc439-cd94-4fff-8d3d-8b3a8b497df0" name="improvements">test
<field id="40ba3f66-e31a-45ae-b6a7-b0331fa79602" name="ended">3/12/2019 16:09
<field id="39729a3d-4409-4b8f-b625-be6551ed2ce1" name="phone_number">test
<field id="b4014f8d-45df-46a7-ac0b-cf8650ffe200" name="name">Test
<field id="e418ed39-039c-4ddd-9af6-d49858c70cc8" name="feedback">test
<field id="e75bbf38-34ba-4478-8077-fc643806a1c6" name="IP">
<field id="d577c664-9dcc-4872-915a-fd04e01b810f" name="started">3/12/2019 16:09
</fields>


What I have tried:

Looking for suggestions at this point. SQL query I've tried:

SELECT dbo_vw_FormResultTCSFeedBack.ResultId
     , dbo_vw_FormResultTCSFeedBack.FormId
     , dbo_vw_FormResultTCSFeedBack.Result
         ,[Result].value('(/Form/Fields/Field)[1]', 'varchar(100)') AS Email
         ,[Result].value('(/Form/Fields/Field)[2]', 'varchar(100)') AS Contact_Customer
         ,[Result].value('(/Form/Fields/Field)[3]', 'varchar(100)') AS Rating
         ,[Result].value('(/Form/Fields/Field)[4]', 'varchar(100)') AS Service_Order_Number
         ,[Result].value('(/Form/Fields/Field)[5]', 'varchar(100)') AS Improvements
         ,[Result].value('(/Form/Fields/Field)[6]', 'varchar(100)') AS End_Date
     , dbo_vw_FormResultTCSFeedBack.IpAddress
     , dbo_vw_FormResultTCSFeedBack.UtcDateCreated
     , dbo_vw_FormResultTCSFeedBack.UserAgent
     , dbo_vw_FormResultTCSFeedBack.UniqueId
FROM dbo_vw_FormResultTCSFeedBack;


The .value lines are the only problems at this point. There is probably a simpler solution that has eluded me thus far.
Posted
Updated 27-Mar-19 7:11am
v4
Comments
RedDk 22-Mar-19 14:12pm
   
You talk about xml but don't show what that xml looks like. This "XML" is typed XML in the database? What SQL tools are at your disposal? Access is cool because queries can be run and, as you suggest new tables from those results can be saved from the queries ... but as far as XML goes there's the main connection to that format through IMPORT and EXPORT facilities built into it. There's a whole HELP topic in the Access {?} "Exporting" queue which covers SQL and table linking. But if you really want the lowdown on XML and Microsoft Office products you'll have to have Excel (spreadsheet/workbook) installed. Check HELP there as well. In Excel the topic samples are most specific and there's the added perk that all four topics, XML, SQL, Access, and Excel get lumped into a nice IMPORT/EXPORT/TABLE tutorial which blasts MSXML (that insideous monster) in favor of obtaining real W3C XML.
Member 14192503 22-Mar-19 14:35pm
   
Thanks for the quick reply. Sorry for the confusion: In the linked table, the whole XML data string is within the [Results] field. When viewed NotePad the field value looks like this:



<fields>
<field id="374b4dd2-7729-432d-9ff8-1966c88a08a1" name="email">test
<field id="baf70494-530a-4f22-bc8d-26c4def99cb2" name="contact_customer">No
<field id="f9649c10-aba5-4ff1-8d43-2860dd7c09f7" name="rating">5
<field id="cc7e34b4-e73c-45e2-8f7a-84fa34b36211" name="service_order_number">
<field id="2cdfc439-cd94-4fff-8d3d-8b3a8b497df0" name="improvements">test
<field id="40ba3f66-e31a-45ae-b6a7-b0331fa79602" name="ended">3/12/2019 16:09
<field id="39729a3d-4409-4b8f-b625-be6551ed2ce1" name="phone_number">test
<field id="b4014f8d-45df-46a7-ac0b-cf8650ffe200" name="name">Test
<field id="e418ed39-039c-4ddd-9af6-d49858c70cc8" name="feedback">test
<field id="e75bbf38-34ba-4478-8077-fc643806a1c6" name="IP">
<field id="d577c664-9dcc-4872-915a-fd04e01b810f" name="started">3/12/2019 16:09

RedDk 22-Mar-19 14:51pm
   
Ok, not sure if this idea will help or hinder, but since you might now have a table ... change it into a "linked" table. Right click EXPORT of that linked table will allow you to contextually package an xsd/xml/other (even html I guess). If you do that, you'll be able to see your data in .html. Can you do this?
Maciej Los 22-Mar-19 15:54pm
   
Next time, please, use "Improve question" widget.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Solution was to treat the XML Column as a (Long) String. Due to limitations in Access, I had to break the extraction into two separate Access Queries and then reassemble the results in a third simple select query. SQL for the two main queries shown below: I'm sure there was a simpler way to do this, but this works and accounts for all variations in the column of data. At least until someone wants to change the source data name identifications.
SELECT dbo_vw_FormResultTCSFeedBack.UniqueId, 

     InStr(1,[dbo_vw_FormResultTCSFeedBack].[Result],"email",1)+7 AS XML1SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML1SCnt]-1,2)="/>",[XML1SCnt],InStr([XML1SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML1ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML1SCnt],[XML1ECnt]-[XML1SCnt]) AS Email, 

     InStr([XML1ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"contact_customer",1)+18 AS XML2SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML2SCnt]-1,2)="/>",[XML2SCnt],InStr([XML2SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML2ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML2SCnt],[XML2ECnt]-[XML2SCnt]) AS Contact_Customer, 

     InStr([XML2ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"rating",1)+8 AS XML3SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML3SCnt]-1,2)="/>",[XML3SCnt],InStr([XML3SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML3ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML3SCnt],[XML3ECnt]-[XML3SCnt]) AS Rating, 

     InStr([XML3ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"service_order_number",1)+22 AS XML4SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML4SCnt]-1,2)="/>",[XML4SCnt],InStr([XML4SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML4ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML4SCnt],[XML4ECnt]-[XML4SCnt]) AS Service_Order_Number, 

     InStr([XML4ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"improvements",1)+14 AS XML5SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML5SCnt]-1,2)="/>",[XML5SCnt],InStr([XML5SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML5ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML5SCnt],[XML5ECnt]-[XML5SCnt]) AS Improvements, 

dbo_vw_FormResultTCSFeedBack.IpAddress, 
dbo_vw_FormResultTCSFeedBack.UtcDateCreated
FROM dbo_vw_FormResultTCSFeedBack;


SELECT dbo_vw_FormResultTCSFeedBack.UniqueId, 

     InStr(1,[dbo_vw_FormResultTCSFeedBack].[Result],'Name="ended',1)+13 AS XML6SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML6SCnt]-1,2)="/>",[XML6SCnt],InStr([XML6SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML6ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML6SCnt],[XML6ECnt]-[XML6SCnt]) AS Ended, 

     InStr([XML6ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"phone_number",1)+14 AS XML7SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML7SCnt]-1,2)="/>",[XML7SCnt],InStr([XML7SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML7ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML7SCnt],[XML7ECnt]-[XML7SCnt]) AS Phone_Number, 

     InStr([XML7ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"Name=",1)+12 AS XML8SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML8SCnt]-1,2)="/>",[XML8SCnt],InStr([XML8SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML8ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML8SCnt],[XML8ECnt]-[XML8SCnt]) AS Name, 

     InStr([XML8ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"feedback",1)+10 AS XML9SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML9SCnt]-1,2)="/>",[XML9SCnt],InStr([XML9SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML9ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML9SCnt],[XML9ECnt]-[XML9SCnt]) AS Feedback, 

     InStr([XML9ECnt],[dbo_vw_FormResultTCSFeedBack].[Result],"started",1)+9 AS XML10SCnt, 
     IIf(Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML10SCnt]-1,2)="/>",[XML10SCnt],InStr([XML10SCnt],[dbo_vw_FormResultTCSFeedBack].[Result],"",1)) AS XML10ECnt, 
          Mid([dbo_vw_FormResultTCSFeedBack].[Result],[XML10SCnt],[XML10ECnt]-[XML10SCnt]) AS Started

FROM dbo_vw_FormResultTCSFeedBack;
   
v2
Comments
Maciej Los 27-Mar-19 14:02pm
   
5ed for self-resolving!
BTW: Please, use green button to accept your answer as a solution. This will remove your question from unanswered list.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190419.4 | Last Updated 27 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100