CODEWITHSUNDEEP
sql-serverxmlt-sqlxquerysql-server-openxml
PyDeveloper
PyDeveloper

Reputation: 319

parsing xml into sql columns

I need to parse values from XML to SQL column's

XML looks like this

<GetCategorySpecificsResponse xmlns="urn:ebay:apis:eBLBaseComponents">
    <Timestamp>2020-11-22T23:17:48.772Z</Timestamp>
    <Ack>Success</Ack>
    <Version>1179</Version>
    <Build>E1179_CORE_API6_19296588_R1</Build>
    <Recommendations>
        <CategoryID>22422</CategoryID>
        <NameRecommendation>
            <Name>Binding</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Cloth</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Disbound</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Fine Binding</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Hardcover</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Leather</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Loose Pages</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Unbound</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Vellum</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Subject</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Art</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Children's</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>History</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Law</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Literature</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Medicine</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Natural Science</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Philosophy</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Reference</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Religion, Bibles</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Science &amp; Technology</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Travel</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Original/Facsimile</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Original</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Facsimile</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Unknown</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Date of Publication</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Unknown</Value>
                <ValidationRules />
            </ValueRecommendation>
            <HelpText>Please indicate the year when the book was published (between 1454-1540)</HelpText>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Language</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Arabic</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Bengali</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Bulgarian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Chinese</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Czech</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Danish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Dutch</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>English</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Finnish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>French</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>German</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Greek</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Hebrew</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Hindi/Urdu</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Hungarian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Irish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Italian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Japanese</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Korean</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Latin</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Latvian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Lithuanian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Malay</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Norwegian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Polish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Portuguese</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Russian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Serbian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Slovenian</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Spanish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Swedish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Turkish</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Vietnamese</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Welsh</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Special Attributes</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>30</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>First Edition</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Illustrated</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Signed</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>With Dust Jacket</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Region</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Africa</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Antarctica</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Asia</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Australia, Oceania</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Europe</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Middle East</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>North America</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>South America</Value>
                <ValidationRules />
            </ValueRecommendation>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Publisher</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Place of Publication</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
        </NameRecommendation>
        <NameRecommendation>
            <Name>Author</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Optional</UsageConstraint>
            </ValidationRules>
        </NameRecommendation>
        <NameRecommendation>
            <Name>California Prop 65 Warning</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <AspectUsage>Instance</AspectUsage>
                <MaxValueLength>800</MaxValueLength>
                <UsageConstraint>Optional</UsageConstraint>
                <VariationSpecifics>Disabled</VariationSpecifics>
            </ValidationRules>
            <HelpText>California Proposition 65 requires businesses to provide warnings to Californians about significant exposure to chemicals that cause cancer, birth defects, or other reproductive harm. Add details about the warning you want to show California buyers. We'll add a warning symbol and the word 'WARNING:' before the description you enter here, and we’ll add 'For more information go to www.P65Warnings.ca.gov' following your description.</HelpText>
        </NameRecommendation>
    </Recommendations>
    <Recommendations>
        <CategoryID>23048</CategoryID>
        <NameRecommendation>
            <Name>Material</Name>
            <ValidationRules>
                <ValueType>Text</ValueType>
                <MaxValues>1</MaxValues>
                <SelectionMode>FreeText</SelectionMode>
                <UsageConstraint>Recommended</UsageConstraint>
            </ValidationRules>
            <ValueRecommendation>
                <Value>Paper</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Papyrus</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Parchment</Value>
                <ValidationRules />
            </ValueRecommendation>
            <ValueRecommendation>
                <Value>Vellum</Value>
            </ValueRecommendation>
        </NameRecommendation>
    </Recommendations>
</GetCategorySpecificsResponse>

What I've tried is:

USE OPENXMLTesting
GO

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML_2
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


SELECT Category_ID, NameRecomendation
FROM OPENXML(@hDoc, 'GetCategorySpecificsResponse/Recomendations')
WITH 
(
Category_ID [varchar](100) 'Category_ID',
NameRecomendation [varchar](100) 'NameRecommendation'
)

EXEC sp_xml_removedocument @hDoc
GO

But i always get empty table. Since i can't parse the first level it's nonsense to go further to get Final solution would contain columns.

  1. CategoryID, NameRecommendation/Name ,
  2. NameRecommendation/ValidationRules/SelectionMode
  3. NameRecommendation/ValidationRules/UsageConstraint
  4. NameRecommendation/ValidationRules/ValueType
  5. NameRecommendation/ValueRecommendation/Value

Upvotes: 0

Views: 69

Answers (1)

Thom A
Thom A

Reputation: 95554

Without expected results, this is a guess, but returns results and should be enough for you to fill in the gaps:

DECLARE @XML = {Your XML};

WITH XMLNAMESPACES(DEFAULT 'urn:ebay:apis:eBLBaseComponents')
SELECT GCSR.R.value('(CategoryID/text())[1]','int') AS CategoryID,
       R.NR.value('(Name/text())[1]','varchar(20)') AS [Name],
       R.NR.value('(ValidationRules/UsageConstraint/text())[1]','varchar(20)') AS UsageConstraint,
       NR.VR.value('(Value/text())[1]','varchar(20)') AS [Value]
FROM (VALUES(@XML))V(X)
     CROSS APPLY V.X.nodes('GetCategorySpecificsResponse/Recommendations')GCSR(R)
     CROSS APPLY GCSR.R.nodes('./NameRecommendation')R(NR)
     CROSS APPLY R.NR.nodes('./ValueRecommendation') NR(VR);

Upvotes: 1

Related Questions