Creating XML Schema for Bulk Load to SQL Server - Child Element Describes Parent

Question

I have an XML document that I'm working to build a schema for in order to bulk load these documents into a SQL Server table. The XML I'm focusing on looks like this:

<Coverage>
    <CoverageCd>BI</CoverageCd>
    <CoverageDesc>BI</CoverageDesc>
    <Limit>
        <FormatCurrencyAmt>
            <Amt>30000.00</Amt>
        </FormatCurrencyAmt>
        <LimitAppliesToCd>PerPerson</LimitAppliesToCd>
    </Limit>
    <Limit>
        <FormatCurrencyAmt>
            <Amt>85000.00</Amt>
        </FormatCurrencyAmt>
        <LimitAppliesToCd>PerAcc</LimitAppliesToCd>
    </Limit>
</Coverage>
<Coverage>
    <CoverageCd>PD</CoverageCd>
    <CoverageDesc>PD</CoverageDesc>
    <Limit>
        <FormatCurrencyAmt>
            <Amt>50000.00</Amt>
        </FormatCurrencyAmt>
        <LimitAppliesToCd>Coverage</LimitAppliesToCd>
    </Limit>
</Coverage>

Inside the Limit element, there's a child LimitAppliesToCd that I need to use to determine where the Amt element's value actually gets stored inside my table. Is this possible to do using the standard XML Bulk Load feature of SQL Server? Normally in XML I'd expect that the element would have an attribute containing the "PerPerson" or "PerAcc" information, but this standard we're using does not call for that.

If anyone has worked with the ACORD standard before, you might know what I'm working with here. Any help is greatly appreciated.

Is this question solved? Do you need further help? Please allow me one hint: It would be very kind of you to tick the acceptance check below the best answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you crossed the 15 points border yourself you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Shnugo, Aug 18 '16 at 10:09

score 0 · Answer 1 · answered Aug 12 '16 at 08:58

Don't know exactly what you are talking about, but this is a solution to get the information out of your XML.

Assumption: Your XML is already bulk-loaded into a declared variable @xml of type XML:

A CTE will pull the information out of your XML. The final query will then use PIVOT to put your data into the right column.

With a fitting table's structure the actual insert should be simple...

WITH DerivedTable AS
(
    SELECT cov.value('CoverageCd[1]','varchar(max)') AS CoverageCd
          ,cov.value('CoverageDesc[1]','varchar(max)') AS CoverageDesc
          ,lim.value('(FormatCurrencyAmt/Amt)[1]','decimal(14,4)') AS Amt
          ,lim.value('LimitAppliesToCd[1]','varchar(max)') AS LimitAppliesToCd
    FROM @xml.nodes('/root/Coverage') AS A(cov)
    CROSS APPLY cov.nodes('Limit') AS B(lim)
)
SELECT p.*
FROM
(SELECT * FROM DerivedTable) AS tbl
PIVOT
(
    MIN(Amt) FOR LimitAppliesToCD IN(PerPerson,PerAcc,Coverage)
) AS p

Creating XML Schema for Bulk Load to SQL Server - Child Element Describes Parent

1 Answers1