0

I am currently trying to get better on AdventureWorks but I've got one issue. Indeed, I would like to use the data from the column "Demographics" on the table Sales_Store but I don't know how I can separate the different elements. Example of one record below:

\<StoreSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"\>
\<AnnualSales\>800000\</AnnualSales\>
\<AnnualRevenue\>80000\</AnnualRevenue\>
\<BankName\>United Security\</BankName\>
\<BusinessType\>BM\</BusinessType\>
\<YearOpened\>1996\</YearOpened\>
\<Specialty\>Mountain\</Specialty\>
\<SquareFeet\>21000\</SquareFeet\>
\<Brands\>2\</Brands\>
\<Internet\>ISDN\</Internet\>
\<NumberEmployees\>13\</NumberEmployees\>
\</StoreSurvey\>

I am working with access but i can also use MySQL.

I already use the the function Split but i don't know why it did not work at all.

SELECT
    Split(Split(Demographics, '<')(1), '>')(0) AS AnnualSales,
    Split(Split(Demographics, '<AnnualRevenue>')(1), '</AnnualRevenue>')(0) AS AnnualRevenue,
    Split(Split(Demographics, '<BankName>')(1), '</BankName>')(0) AS BankName,
    Split(Split(Demographics, '<BusinessType>')(1), '</BusinessType>')(0) AS BusinessType,
    Split(Split(Demographics, '<YearOpened>')(1), '</YearOpened>')(0) AS YearOpened,
    Split(Split(Demographics, '<Specialty>')(1), '</Specialty>')(0) AS Specialty,
    Split(Split(Demographics, '<SquareFeet>')(1), '</SquareFeet>')(0) AS SquareFeet,
    Split(Split(Demographics, '<Brands>')(1), '</Brands>')(0) AS Brands,
    Split(Split(Demographics, '<Internet>')(1), '</Internet>')(0) AS Internet,
    Split(Split(Demographics, '<NumberEmployees>')(1), '</NumberEmployees>')(0) AS NumberEmployees
FROM
    Sales_Store

SELECT
    ExtractValue(Demographics, 'StoreSurvey/AnnualSales') AS AnnualSales,
    ExtractValue(Demographics, 'StoreSurvey/AnnualRevenue') AS AnnualRevenue,
    ExtractValue(Demographics, 'StoreSurvey/BankName') AS BankName,
    ExtractValue(Demographics, 'StoreSurvey/BusinessType') AS BusinessType,
    ExtractValue(Demographics, 'StoreSurvey/YearOpened') AS YearOpened,
    ExtractValue(Demographics, 'StoreSurvey/Specialty') AS Specialty,
    ExtractValue(Demographics, 'StoreSurvey/SquareFeet') AS SquareFeet,
    ExtractValue(Demographics, 'StoreSurvey/Brands') AS Brands,
    ExtractValue(Demographics, 'StoreSurvey/Internet') AS Internet,
    ExtractValue(Demographics, 'StoreSurvey/NumberEmployees') AS NumberEmployees
FROM
    Sales_Store

The link for the database is right there https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak

HUCHR
  • 1
  • 2
  • Do you get error when attempting query? AFAIK, Access cannot execute Split() in query. What is the code for ExtractValue() function? – June7 Jun 12 '23 at 19:04
  • Hi thanks for you help but unfortunately, in the link, they say that they have to create data warehouse using Teradata. And I don't know anything about this tool and I would like to learn in SQL... – HUCHR Jun 12 '23 at 20:06
  • Right, I deleted earlier comment. I just learned AdventureWorks is simply a sample database provided with SQLServer. However, you mention MySQL. – June7 Jun 12 '23 at 20:07
  • Indeed I have an error when i load the query. Do you have any idea of what is the access function of "SPLIT" then? I do not understand your last sentance..Sorry – HUCHR Jun 12 '23 at 20:07
  • Split() is intrinsic VBA function. I use it to parse strings to an array. Or with the abbreviated method you posted to grab a single element from delimited string. And deleted sentence from previous comment. You are quick. – June7 Jun 12 '23 at 20:12
  • Does this answer your question? [Assistance with AdventureWorks2019 query error "XQuery \[Sales.Store.Demographics.value()\]: There is no element named 'StoreSurvey'"](https://stackoverflow.com/questions/76364248/assistance-with-adventureworks2019-query-error-xquery-sales-store-demographics) – June7 Jun 12 '23 at 20:13
  • Connect Access to SQLServer (MySQL ?) table. Access can run pass-through query on SQLServer table. I don't know about MySQL. Maybe this https://stackoverflow.com/questions/66620064/invalid-connection-string-in-pass-through-query-in-ms-access-vba – June7 Jun 12 '23 at 20:17
  • In fact, I would like to create a new column for each information you can find in the column Demographics. For exemple if it was a CSV I would like to transform Day / Month / Year to Column Day Column Month and Column Year. Actually it was an accident ahah – HUCHR Jun 12 '23 at 20:20
  • It seems that you last link is quiete great ! I will try this and I will come back here to give an update. Many thanks for your help – HUCHR Jun 12 '23 at 20:28
  • Most here won't download files. Everything to analyze issue should be provided in question. Again, what is ExtractValue() - a custom function? I am not interested in trying to set up another DB under SQLServer (I don't have MySQL). Still something of a mystery to me why an XML file is involved. – June7 Jun 12 '23 at 20:28

0 Answers0