Блог

Главная - Вопросы по программированию - синтаксический анализ xml в столбцы sql

синтаксический анализ xml в столбцы sql

#sql-server #xml #tsql #xquery #sql-server-openxml

#sql-сервер #xml #tsql #xquery #sql-server-openxml

Вопрос:

Мне нужно проанализировать значения из XML в столбцы SQL

XML выглядит следующим образом

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

Что я пробовал, так это:

 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

Но я всегда получаю пустую таблицу. Поскольку я не могу проанализировать первый уровень, бессмысленно идти дальше, чтобы получить
Окончательное решение будет содержать столбцы.

  1. CategoryID, NameRecommendation /Name ,
  2. NameRecommendation / ValidationRules /SelectionMode
  3. NameRecommendation / ValidationRules/UsageConstraint
  4. NameRecommendation / ValidationRules/ValueType
  5. NameRecommendation / ValueRecommendation /Значение

Комментарии:

1. sp_xml_preparedocument действительно старый способ разбора XML — это SQL Server; XQuery доступен, по крайней мере, с SQL Server 2008 (возможно, даже с 2005).

2. Каковы ваши ожидаемые результаты и здесь?

3. @Larnu сначала нужно было бы получить все доступные CategoryID из XML .. И последний подход — получить эти 5 столбцов, перечисленных в вопросе

4. Прежде всего, вам нужно исправить XML; он недействителен. Вот почему то, что у вас есть, тоже не работает. Для анализа XML он должен быть действительным. В приведенном выше, например, GetCategorySpecificsResponse никогда не закрывается.

5. @Larnu извините. Я просто скопировал несколько узлов. Размер файла превышает 1,2 ГБ. Я исправил отсутствующий тег

Ответ №1:

Без ожидаемых результатов это предположение, но возвращает результаты, и их должно быть достаточно для заполнения пробелов:

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

Комментарии:

1. Это больше, чем мне нужно. Спасибо за ваше время

2. просто чтобы иметь в виду. Вывод более 10 миллионов строк за 3 минуты. Хорошая работа