Load XML Data into SQL

ARProfile 41 Reputation points
2022-05-29T10:47:48.763+00:00

Hi,

I have a requirement to present the data from an xml file in table form. A snippet from the xml file is below. The values that are needed are:

name e.g. cpe:/a:%240.99_kindle_books_project:%240.99_kindle_books:6::~~~android~~
title e.g. $0.99 Kindle Books project $0.99 Kindle Books (aka com.kindle.books.for99) for android 6.0

I require the SQL query to present the data in table form, so I may insert the records into my table. I require the SQL query to get this data and don't want to use any other programming languages.

XML SNIPPET:

<?xml version='1.0' encoding='UTF-8'?>
<cpe-list xmlns:config="http://scap.nist.gov/schema/configuration/0.1" xmlns="http://cpe.mitre.org/dictionary/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.3" xmlns:cpe-23="http://scap.nist.gov/schema/cpe-extension/2.3" xmlns:ns6="http://scap.nist.gov/schema/scap-core/0.1" xmlns:meta="http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2" xsi:schemaLocation="http://scap.nist.gov/schema/cpe-extension/2.3 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary-extension_2.3.xsd http://cpe.mitre.org/dictionary/2.0 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary_2.3.xsd http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2 https://scap.nist.gov/schema/cpe/2.1/cpe-dictionary-metadata_0.2.xsd http://scap.nist.gov/schema/scap-core/0.3 https://scap.nist.gov/schema/nvd/scap-core_0.3.xsd http://scap.nist.gov/schema/configuration/0.1 https://scap.nist.gov/schema/nvd/configuration_0.1.xsd http://scap.nist.gov/schema/scap-core/0.1 https://scap.nist.gov/schema/nvd/scap-core_0.1.xsd">
<generator>
<product_name>National Vulnerability Database (NVD)</product_name>
<product_version>4.10</product_version>
<schema_version>2.3</schema_version>
<timestamp>2022-05-24T03:51:08.065Z</timestamp>
</generator>
<cpe-item name="cpe:/a:%240.99_kindle_books_project:%240.99_kindle_books:6::~~~android~~">
<title xml:lang="en-US">$0.99 Kindle Books project $0.99 Kindle Books (aka com.kindle.books.for99) for android 6.0</title>
<references>
<reference href="https://play.google.com/store/apps/details?id=com.kindle.books.for99">Product information</reference>
<reference href="https://docs.google.com/spreadsheets/d/1t5GXwjw82SyunALVJb2w0zi3FoLRIkfGPc7AMjRF0r4/edit?pli=1#gid=1053404143">Government Advisory</reference>
</references>
<cpe-23:cpe23-item name="cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:::::android::"/>
</cpe-item>
<cpe-item name="cpe:/a:zzzcms:zzzphp:2.0.5">
<title xml:lang="en-US">ZZZCMS zzzphp 2.0.5</title>
<references>
<reference href="http://www.zzzcms.com/a/news/list_31_1.html">Version</reference>
<reference href="http://www.zzzcms.com/index.html">Vendor</reference>
</references>
<cpe-23:cpe23-item name="cpe:2.3:a:zzzcms:zzzphp:2.0.5:::::::*"/>
</cpe-item>
</cpe-list>

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 24,911 Reputation points
    2022-05-29T13:40:26.917+00:00

    Hi @ARProfile ,

    Process of converting XML into a rectangular/relational format is called shredding.

    It is not clear what is a source of the input XML:

    • T-SQL variable.
    • Table column of XML data type.
    • XML file on the file system.

    I am assuming it is a variable.

    SQL 

    DECLARE @xml XML =  
'<?xml version="1.0" encoding="UTF-8"?>  
<cpe-list xmlns:config="http://scap.nist.gov/schema/configuration/0.1" xmlns="http://cpe.mitre.org/dictionary/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:scap-core="http://scap.nist.gov/schema/scap-core/0.3"  
          xmlns:cpe-23="http://scap.nist.gov/schema/cpe-extension/2.3" xmlns:ns6="http://scap.nist.gov/schema/scap-core/0.1" xmlns:meta="http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2"  
          xsi:schemaLocation="http://scap.nist.gov/schema/cpe-extension/2.3 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary-extension_2.3.xsd http://cpe.mitre.org/dictionary/2.0 https://scap.nist.gov/schema/cpe/2.3/cpe-dictionary_2.3.xsd http://scap.nist.gov/schema/cpe-dictionary-metadata/0.2 https://scap.nist.gov/schema/cpe/2.1/cpe-dictionary-metadata_0.2.xsd http://scap.nist.gov/schema/scap-core/0.3 https://scap.nist.gov/schema/nvd/scap-core_0.3.xsd http://scap.nist.gov/schema/configuration/0.1 https://scap.nist.gov/schema/nvd/configuration_0.1.xsd http://scap.nist.gov/schema/scap-core/0.1 https://scap.nist.gov/schema/nvd/scap-core_0.1.xsd">  
 <generator>  
 <product_name>National Vulnerability Database (NVD)</product_name>  
 <product_version>4.10</product_version>  
 <schema_version>2.3</schema_version>  
 <timestamp>2022-05-24T03:51:08.065Z</timestamp>  
 </generator>  
 <cpe-item name="cpe:/a:%240.99_kindle_books_project:%240.99_kindle_books:6::~~~android~~">  
 <title xml:lang="en-US">$0.99 Kindle Books project $0.99 Kindle Books (aka com.kindle.books.for99) for android 6.0</title>  
 <references>  
 <reference href="https://play.google.com/store/apps/details?id=com.kindle.books.for99">Product information</reference>  
 <reference href="https://docs.google.com/spreadsheets/d/1t5GXwjw82SyunALVJb2w0zi3FoLRIkfGPc7AMjRF0r4/edit?pli=1#gid=1053404143">Government Advisory</reference>  
 </references>  
 <cpe-23:cpe23-item name="cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:::::android::"/>  
 </cpe-item>  
 <cpe-item name="cpe:/a:zzzcms:zzzphp:2.0.5">  
 <title xml:lang="en-US">ZZZCMS zzzphp 2.0.5</title>  
 <references>  
 <reference href="http://www.zzzcms.com/a/news/list_31_1.html">Version</reference>;  
 <reference href="http://www.zzzcms.com/index.html">Vendor</reference>;</references>  
 <cpe-23:cpe23-item name="cpe:2.3:a:zzzcms:zzzphp:2.0.5:::::::*"/>  
 </cpe-item>  
</cpe-list>';  
  
;WITH XMLNAMESPACES(DEFAULT 'http://cpe.mitre.org/dictionary/2.0')  
SELECT c.value('@name', 'VARCHAR(200)') AS [name]   
 , c.value('(title/text())[1]', 'VARCHAR(200)') AS title   
FROM @xml.nodes('/cpe-list/cpe-item') AS t(c);

    SQL #2
    Loads XML file from the file system. 

    WITH XMLNAMESPACES(DEFAULT 'http://cpe.mitre.org/dictionary/2.0')  
   , rs (xmlData) AS  
(  
   SELECT TRY_CAST(BulkColumn AS XML)   
   FROM OPENROWSET(BULK N'e:\Temp\ARProfile-3844.xml', SINGLE_BLOB) AS x  
)  
-- uncomment line below when you are ready  
--INSERT INTO destinationTable (Col_1, Col_2)  
SELECT c.value('@name', 'VARCHAR(200)') AS [name]   
	, c.value('(title/text())[1]', 'VARCHAR(200)') AS title   
FROM rs   
   CROSS APPLY xmlData.nodes('/cpe-list/cpe-item') AS t(c);
    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ARProfile 41 Reputation points
    2022-05-29T14:10:06.007+00:00

    Hi, thank you for taking the time to respond to this. The source of the XML is an XML file on the filesystem. Would you mind updating the query for this?

  2. Erland Sommarskog 100.8K Reputation points MVP
    2022-05-29T14:15:26.06+00:00

    Hi, thank you for taking the time to respond to this. The source of the XML is an XML file on the filesystem. Would you mind updating the query for this?

    If so you need to first load the file: 

    SELECT @xml = T.c
FROM  OPENROWSET(BULK 'filepathhere', SINGLE_CLOB) AS T(c)

    Then you can use Yitzhak's query.

    0 comments
  3. ARProfile 41 Reputation points
    2022-05-29T15:16:21.827+00:00

    Perfect. Thanks both. It seems some further work is needed to reconcile the CPE value between the above and the following: https://nvd.nist.gov/feeds/json/cve/1.1/nvdcve-1.1-modified.json.zip

    But that may be a query for elsewhere.

    Thanks again!

    0 comments