How do I import XML to a SQL Server table?

Question

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:

<WEBSITES>
 <WEBSITE>
 <WEBSITE_ID>sta001</WEBSITE_ID>
 <WEBSITE_ALTERNATE_ID/>
 <WEBSITE_VERSION>4</WEBSITE_VERSION>
 <TYPE>DYNAMIC</TYPE>
 <NAME>TEST WEBSITE</NAME>
 <WEBSITE_DETAILS>
 <WEBSITE_DETAIL>
 <NAME>COST</NAME>
 <VALUE>500</VALUE>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 <WEBSITE_DETAIL>
 <NAME>LANGUAGE</NAME>
 <VALUE>EN</VALUE>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 <WEBSITE_DETAIL>
 <NAME>DATABASE</NAME>
 <VALUE/>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 </WEBSITE_DETAILS>
 </WEBSITE>
 <WEBSITE>
 <WEBSITE_ID>mmn023</WEBSITE_ID>
 <WEBSITE_ALTERNATE_ID/>
 <WEBSITE_VERSION>3</WEBSITE_VERSION>
 <TYPE>DYNAMIC</TYPE>
 <NAME>TEST WEBSITE 2</NAME>
 <WEBSITE_DETAILS>
 <WEBSITE_DETAIL>
 <NAME>COST</NAME>
 <VALUE>750</VALUE>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 <WEBSITE_DETAIL>
 <NAME>LANGUAGE</NAME>
 <VALUE>RU</VALUE>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 <WEBSITE_DETAIL>
 <NAME>DATABASE</NAME>
 <VALUE>TRUE</VALUE>
 <INHERITED>false</INHERITED>
 </WEBSITE_DETAIL>
 </WEBSITE_DETAILS>
 </WEBSITE>
</WEBSITES>

Here is the stored procedure I am using:

USE [websitesDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_website_import] (
 @xmlData XML ,
 @retValue varchar(100) OUTPUT
)
AS
BEGIN
SET @retValue='Failed';
INSERT INTO [websites](
[Web_ID],
[Web_Version],
[Web_Type],
[Web_Name]
)
SELECT
[Table].[Column].value('WEBSITE_ID [1]', 'nvarchar(100)'),
[Table].[Column].value('WEBSITE_VERSION [1]', 'nvarchar(100)'),
[Table].[Column].value('TYPE [1]', 'nvarchar(100)'),
[Table].[Column].value('NAME [1]', 'nvarchar(100)')
 FROM @xmlData.nodes('/ WEBSITES / WEBSITE') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
 SET @retValue='SUCCESS';
INSERT INTO [website_details](
[Web_ID],
[cost],
[language],
[database]
)
SELECT
[Table].[Column].value('WEBSITE_ID [1]', 'nvarchar(100)'),
[Table].[Column].value('COST [1]', 'nvarchar(100)'),
[Table].[Column].value('LANGUAGE [1]', 'nvarchar(100)'),
[Table].[Column].value('DATABASE [1]', 'nvarchar(100)')
 FROM @xmlData.nodes('/ WEBSITES / WEBSITE / WEBSITE_DETAILS') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
 SET @retValue='SUCCESS'

;

Answer 1

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

Answer 2

Necromancing.
If anybody needs to have it file and variable-less (good for table-valued function):

SELECT 
 --myTempTable.XmlCol.value('.', 'varchar(36)') AS val 
 myTempTable.XmlCol.query('./ID').value('.', 'varchar(36)') AS ID 
 ,myTempTable.XmlCol.query('./Name').value('.', 'nvarchar(MAX)') AS Name 
 ,myTempTable.XmlCol.query('./RFC').value('.', 'nvarchar(MAX)') AS RFC 
 ,myTempTable.XmlCol.query('./Text').value('.', 'nvarchar(MAX)') AS Text 
 ,myTempTable.XmlCol.query('./Desc').value('.', 'nvarchar(MAX)') AS Description 
 --,myTempTable.XmlCol.value('(Desc)[1]', 'nvarchar(MAX)') AS DescMeth2
FROM 
(
 SELECT 
 CAST('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <data-set>
 <record>
 <ID>1</ID>
 <Name>A</Name>
 <RFC>RFC 1035[1]</RFC>
 <Text>Address record</Text>
 <Desc>Returns a 32-bit IPv4 address, most commonly used to map hostnames to an IP address of the host, but it is also used for DNSBLs, storing subnet masks in RFC 1101, etc.</Desc>
 </record>
 <record>
 <ID>2</ID>
 <Name>NS</Name>
 <RFC>RFC 1035[1]</RFC>
 <Text>Name server record</Text>
 <Desc>Delegates a DNS zone to use the given authoritative name servers</Desc>
 </record>
 </data-set>
 ' AS xml) AS RawXml
) AS b 
--CROSS APPLY b.RawXml.nodes('//record/ID') myTempTable(XmlCol);
CROSS APPLY b.RawXml.nodes('//record') myTempTable(XmlCol);

or directly from file (table valued function)

SELECT 
 --myTempTable.XmlCol.value('.', 'varchar(36)') AS val 
 myTempTable.XmlCol.query('./ID').value('.', 'varchar(36)') AS ID 
 ,myTempTable.XmlCol.query('./Name').value('.', 'nvarchar(MAX)') AS Name 
 ,myTempTable.XmlCol.query('./RFC').value('.', 'nvarchar(MAX)') AS RFC 
 ,myTempTable.XmlCol.query('./Text').value('.', 'nvarchar(MAX)') AS Text 
 ,myTempTable.XmlCol.query('./Desc').value('.', 'nvarchar(MAX)') AS Description 
FROM 
(
 SELECT CONVERT(XML, BulkColumn) AS RawXml 
 FROM OPENROWSET(BULK 'D:\username\Desktop\MyData.xml', SINGLE_BLOB) AS RowSetName 
) AS b 
CROSS APPLY b.RawXml.nodes('//record') myTempTable(XmlCol)

e.g.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tfu_RPT_SEL_XmlData]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[tfu_RPT_SEL_XmlData]
GO
CREATE FUNCTION [dbo].[tfu_RPT_SEL_XmlData]
(
 @in_language varchar(10) 
 ,@in_reportingDate datetime 
)
RETURNS TABLE
AS
RETURN 
( 
 SELECT 
 --myTempTable.XmlCol.value('.', 'varchar(36)') AS val 
 myTempTable.XmlCol.query('./ID').value('.', 'varchar(36)') AS ID 
 ,myTempTable.XmlCol.query('./Name').value('.', 'nvarchar(MAX)') AS Name 
 ,myTempTable.XmlCol.query('./RFC').value('.', 'nvarchar(MAX)') AS RFC 
 ,myTempTable.XmlCol.query('./Text').value('.', 'nvarchar(MAX)') AS Text 
 ,myTempTable.XmlCol.query('./Desc').value('.', 'nvarchar(MAX)') AS Description 
 FROM 
 (
 SELECT CONVERT(XML, BulkColumn) AS RawXml 
 FROM OPENROWSET(BULK 'D:\username\Desktop\MyData.xml', SINGLE_BLOB) AS RowSetName 
 ) AS b 
 CROSS APPLY b.RawXml.nodes('//record') myTempTable(XmlCol)
)
GO
SELECT * FROM tfu_RPT_SEL_XmlData('DE', CURRENT_TIMESTAMP)