How do I import XML to a SQL Server table?

Can anyone help me work towards fixing my solution to import my XML data into a SQL Server Table? I have done my research, but this task has been very hard for me to accomplish. Everything I have found has worked for simple data, but my name and value are separate in the "website_details" section. This is why my script has not been working. I have around 200 XML files, some with more than a thousand records each to import, so I cannot change their structure. The data I am working with is considered confidential, so I have changed the value names and values to enable me to post this.

The first insert command for the websites table works perfectly and imports all of my data. The issue I am having is with the second insert command. The @xmlData.nodes definition is where I think the issue is. In the "website_details" section I am having a hard time defining the structure because the name and value are separate unlike the other information.

Just to give an overview of my database at the moment it consists of two tables. They are websites and website_details. The Web_ID column is included in both tables and is the foreign key that connects website_details to websites. I also have a view that I am using to combine my data called website_view.

Here is sample data pulled from my XML file:

```


sta001

4
DYNAMIC
TEST WEBSITE


COST
500
false


LANGUAGE
EN
false


DATABASE

false




mmn023

3
DYNAMIC
TEST WEBSITE 2


COST
750
false


LANGUAGE
RU
false


DATABASE

You should shred on /WEBSITES/WEBSITE in your second query as well and use a predicate against the NAME node in the values clause and then get the value from VALUE node.

select S.X.value('(WEBSITE_ID/text())[1]', 'nvarchar(100)') as WEBSITE_ID,
S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "COST"]/VALUE/text())[1]', 'nvarchar(100)'),
S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "LANGUAGE"]/VALUE/text())[1]', 'nvarchar(100)'),
S.X.value('(WEBSITE_DETAILS/WEBSITE_DETAIL[(NAME/text())[1] eq "DATABASE"]/VALUE/text())[1]', 'nvarchar(100)')
from @xmlData.nodes('/WEBSITES/WEBSITE') as S(X)


Result:

sta001 500 EN NULL
mmn023 750 RU TRUE


StackExchange Database Administrators Q#106222, answer score: 11