I have written a script that loads an xml file and parse it.

This is the script and a partial xml file as example:

SQL SCRIPT

DECLARE @XML AS XML
DECLARE @hDoc AS INT

CREATE TABLE #XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO #XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\temp\wordpress.2017-05-22.xml', SINGLE_BLOB) AS x;

SELECT @XML = XMLData FROM #XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, 'rss/channel/item')
WITH 
    (       
        Product [VARCHAR](50) 'title',
        Category [VARCHAR](50) 'category'
    )

EXEC sp_xml_removedocument @hDoc

DROP TABLE #XMLwithOpenXML

XML FILE ( Partial )

<?xml version="1.0" encoding="UTF-8" ?>
<rss version="2.0"
    xmlns:excerpt="http://wordpress.org/export/1.2/excerpt/"
    xmlns:content="http://purl.org/rss/1.0/modules/content/"
    xmlns:wfw="http://wellformedweb.org/CommentAPI/"
    xmlns:dc="http://purl.org/dc/elements/1.1/"
    xmlns:wp="http://wordpress.org/export/1.2/"
>
<channel>
    <item>
        <title>Bancone Tetris</title>
        <category domain="categorie-prodotti" nicename="arredi-light"><![CDATA[Arredi light]]></category>
        <category domain="categorie-outlet" nicename="arredi-light"><![CDATA[arredi light]]></category>
        <category domain="categorie-prodotti" nicename="banconi"><![CDATA[Banconi]]></category>
        <category domain="categorie-outlet" nicename="banconi"><![CDATA[Banconi]]></category>
        <category domain="categorie-outlet" nicename="luci"><![CDATA[luci]]></category>
        <wp:postmeta>
            <wp:meta_key><![CDATA[wpcf-codice]]></wp:meta_key>
            <wp:meta_value><![CDATA[cod.05-008]]></wp:meta_value>
        </wp:postmeta>
        <wp:postmeta>
            <wp:meta_key><![CDATA[wpcf-size]]></wp:meta_key>
            <wp:meta_value><![CDATA[145x80xh110 cm]]></wp:meta_value>
        </wp:postmeta>
</item>
<item>
... SAME STRUCTURE AS ABOVE
</item>
</channel>
</rss>

The problem I faced

1. I need to list all products with all the related category, in this moment the script returns all the products with only the first category.
2. I don't know how to return all the postmeta ( key , value ) list for each product...

Hope its clear, thanks to support!