question

ARProfile-3844 avatar image
0 Votes"
ARProfile-3844 asked ARProfile-3844 answered

Load XML Data into SQL

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>

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @ARProfile-3844,

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ARProfile-3844 avatar image
0 Votes"
ARProfile-3844 answered YitzhakKhabinsky-0887 commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ARProfile-3844,

I updated the answer with the SQL #2 section.
Check it out.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ARProfile-3844 avatar image
0 Votes"
ARProfile-3844 answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.