Member 14192503 Ответов: 1

Лучший способ обработки данных столбцов XML в access

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>


Что я уже пробовал:

Ищу предложения на данный момент. 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;


На данный момент единственными проблемами являются строки .value. Вероятно, есть более простое решение, которое до сих пор ускользало от меня.

RedDk

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

Спасибо за быстрый ответ. Извините за путаницу: В связанной таблице вся строка XML-данных находится в поле [Results]. При просмотре Блокнота значение поля выглядит следующим образом:



<поля>
&ЛТ;поле id="374b4dd2-7729-432d-9ff8-1966c88a08a1" название="отправить"и gt;Тест
<field id="baf70494-530a-4f22-bc8d-26c4def99cb2" name="contact_customer">No
<field id="f9649c10-aba5-4ff1-8d43-2860dd7c09f7" name="рейтинг">5
<field id="cc7e34b4-e73c-45e2-8f7a-84fa34b36211" name="service_order_number">
<идентификатор поля="2cdfc439-cd94-4fff-8d3d-8b3a8b497df0" имя="улучшения">Тест
<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="имя">Тест
<идентификатор поля="e418ed39-039c-4ddd-9af6-d49858c70cc8" имя="обратная связь">Тест
<идентификатор поля="e75bbf38-34ba-4478-8077-fc643806a1c6" name="IP">
<field id="d577c664-9dcc-4872-915a-fd04e01b810f" name="started">3/12/2019 16:09

RedDk

Ладно, не уверен, поможет ли эта идея или помешает, но поскольку теперь у вас может быть стол ... измените его на "связанную" таблицу. Экспорт Правой Кнопкой Мыши этой связанной таблицы позволит вам контекстуально упаковать xsd/xml/other (даже html, я думаю). Если вы сделаете это, вы сможете увидеть свои данные в формате .html. Ты можешь это сделать?

Maciej Los

В следующий раз, пожалуйста, используйте виджет "улучшить вопрос".

1 Ответов

Рейтинг:
7

Member 14192503

Решение состояло в том, чтобы рассматривать столбец XML как (длинную) строку. Из - за ограничений в доступе мне пришлось разбить извлечение на два отдельных запроса доступа, а затем собрать результаты в третьем простом запросе select. SQL для двух основных запросов, показанных ниже: Я уверен, что был более простой способ сделать это, но это работает и учитывает все вариации в столбце данных. По крайней мере, до тех пор, пока кто-то не захочет изменить идентификацию имен исходных данных.

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;

Maciej Los

5ed для саморазрешения!
Кстати: пожалуйста, используйте зеленую кнопку, чтобы принять ваш ответ в качестве решения. Это позволит удалить ваш вопрос из списка без ответа.