在访问中处理XML列数据的最佳方法 [英] Best way to handle XML column data in access

问题描述

我通过Access连接到SQL DB以收集调查回复数据。来自单个调查响应的所有数据都包含在链接的SQL DB视图中的单个XML列中。我认为提取节点和数据的最佳方法是使用VBA脚本，我将XML列视为长文本字符串，搜索并提取所需信息并将其写入另一个访问表。这似乎是一个漫长而繁琐的项目，它依赖于XML数据字符串格式结构不会改变，或者必须重新编码。我的另一个想法是在链接的SQL表上使用select查询，但我尝试的SQL视图似乎不起作用。

 <  字段 >  
 <  字段   < span class =code-attribute> id   =  374b4dd2-7729-432d-9ff8-1966c88a08a1    名称  = < span class =code-keyword> email >  test 
 <  字段    id   =  baf70494-530a-4f22-bc8d-26 c4def99cb2   名称  =  contact_customer > 否
 <  字段    id   =  f9649c10-aba5-4ff1-8d43-2860dd7c09f7    name   =  rating < span class =code-keyword>>  5 
 <   field     id   =  cc7e34b4-e73c-45e2-8f7a-84fa34b36211    name   =  service_order_number >  
 <   field     id   =  2cdfc439-cd94-4fff-8d3d-8b3a8b497df0    name   = 改进 >  test 
 <  字段    id   =  40ba3f66-e31a-45ae-b6a7-b0331fa79602    name   = 已结束 >  3/12/2019 16： 09 
 <  字段    id   =  39729a3d-4409-4b8f-b625-be6551ed2ce1    名称  =  phone_number >  test 
 <  字段    id   =  b4014f8d-45df-46a7-ac0b-cf8650ffe200   名称  =  nam e > 测试
 <   field     id   =  e418ed39-039c-4ddd-9af6-d49858c70cc8   姓名  =  feedback >  test 
 <   field     id   =  e75bbf38-34ba-4478 -8077-fc643806a1c6   名称  =   IP >  
 <  字段    id   =  d577c664-9dcc-4872-915a-fd04e01b810f   名称  = 已开始 >  3/12/2019 16:09 
 <   / fields  >  

我尝试过：



此时正在寻找建议。 SQL查询我试过了：

  SELECT  dbo_vw_FormResultTCSFeedBack.ResultId 
，dbo_vw_FormResultTCSFeedBack.FormId 
，dbo_vw_FormResultTCSFeedBack.Result 
，[Result] .value（' （/ Form / Fields / Field）[1]'，'  varchar（100）'） AS 电子邮件
，[结果] .value（' （/ Form / Fields / Field）[2]'，'  varchar（100 ）'） AS  Contact_Customer 
，[Result] .value（' < span class =code-string>（/ Form / Fields / Field）[3]'，'  varchar（100）'） AS 评级
 ，[结果] .value（' （/ Form / Fields / Field）[4]'，< span class =code-string>'  varchar（100）'） AS  Service_Order_Number 
，[结果] .value（' （/ Form / Fields / Field）[5]'，'  varchar（100）'） AS 改进
，[结果] .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; 

.value行是此时唯一的问题。到目前为止，我可能还有一个更简单的解决方案。

解决方案

解决方案是将XML列视为（长）字符串。由于Access的限制，我不得不将提取分解为两个单独的Access Queries，然后在第三个简单的select查询中重新组合结果。下面显示的两个主要查询的SQL：我确信有一种更简单的方法可以做到这一点，但这可以解决数据列中的所有变化。至少在有人想要更改源数据名称标识之前。

 SELECT dbo_vw_FormResultTCSFeedBack.UniqueId，
 
 InStr（1，[dbo_vw_FormResultTCSFeedBack]。[结果]，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 XML3S Cnt，
 IIf（中（[dbo_vw_FormResultTCSFeedBack]。[结果]，[XML3SCnt] -1,2）=/>，[XML3SCnt]，InStr（[XML3SCnt]，[dbo_vw_FormResultTCSFeedBack]。[结果] ，，1））AS XML3ECnt，
 Mid（[dbo_vw_FormResultTCSFeedBack]。[Result]，[XML3SCnt]，[XML3ECnt]  -  [XML3SCnt]）AS等级，
 
 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]。[结果]，改进，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_FormRe sultTCSFeedBack]。[Result]，[XML5SCnt]，[XML5ECnt]  -  [XML5SCnt]）AS改进，
 
 dbo_vw_FormResultTCSFeedBack.IpAddress，
 dbo_vw_FormResultTCSFeedBack.UtcDateCreated 
 FROM dbo_vw_FormResultTCSFeedBack; 
 
 
 SELECT dbo_vw_FormResultTCSFeedBack.UniqueId，
 
 InStr（1，[dbo_vw_FormResultTCSFeedBack]。[Result]，'Name =ends'，1）+13 AS XML6SCnt ，
 IIf（中（[dbo_vw_FormResultTCSFeedBack]。[结果]，[XML6SCnt] -1,2）=/>，[XML6SCnt]，InStr（[XML6SCnt]，[dbo_vw_FormResultTCSFeedBack]。[结果]， ，1））AS XML6ECnt，
 Mid（[dbo_vw_FormResultTCSFeedBack]。[Result]，[XML6SCnt]，[XML6ECnt]  -  [XML6SCnt]）AS Ended，
 
 InStr（[XML6ECnt] ]，[dbo_vw_FormResultTCSFeedBack]。[结果]，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]。[结果]，[XML8SCnt] -1,2）=/>，[XML8SCnt]，InStr（[XML8SCnt]，[dbo_vw_FormResultTCSFeedBack]。[Result]，，1））AS XML8ECnt，
 Mid（[dbo_vw_FormResultTCSFeedBack]。[结果]，[XML8SCnt]，[XML8ECnt]  -  [XML8SCnt]）AS名称，
 
 InStr（[XML8ECnt]，[dbo_vw_FormResultTCSFeedBack]。[结果]，反馈，1）+10 AS XML9SCnt ，
 IIf（中（[dbo_vw_FormResultTCSFeedBack]。[结果]，[XML9SCnt] -1,2）=/>，[XML9SCnt]，InStr（[XML9SCnt]，[dbo_vw_FormResultTCSFeedBack]。[结果]， ，1））AS XML9ECnt，
 Mid（[dbo_vw_FormResultTCSFeedBack]。[Result]，[XML9SCnt]，[XML9ECnt]  -  [XML9SCnt]）AS反馈，
 
 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已启动
 
 FROM dbo_vw_Form ResultTCSFeedBack; 

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.

解决方案

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;

这篇关于在访问中处理XML列数据的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！