Best way to handle XML column data in access

Question

0.00/5 (No votes)

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.

XML

<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:

SQL

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 22-Mar-19 6:27am

Member 14192503

Updated 27-Mar-19 7:11am

RedDk

v4

Add a Solution

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

Add a Solution

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

Member 14192503 · Accepted Answer · 2019-03-27T07:11:00

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;

Best way to handle XML column data in access

1 solution

Solution 1

Add your solution here

Preview 0

Best way to handle XML column data in access

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us