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);