CODEWITHSUNDEEP
xmlsql-server-2008
Raju
Raju

Reputation: 1

Reading the attributes and elements in an XML using Openxml in SQL Server 2008

I have the XML shown below. I want to read the XML attributes and the elements using openxml, please could you help me out?

<Report>
     <Datapoints>
             <Datapoint>10</Datapoint>
             <Datapoint>11</Datapoint>
     </Datapoints>
<Filters>                 
        <Filter Id="5" FilterTypeId="4">            
            <Option2>21</Option2>
            <Option2>22</Option2>             
            <Option2>23</Option2>
        </Filter>        
        <Filter Id="6" FilterTypeId="4">            
            <Option2>21</Option2>
            <Option2>22</Option2>             
            <Option2>23</Option2>
        </Filter>   
    </Filters>           
</Report>

I want to read the attributes in tag Filter (Id and FilterTypeId) along with element option2.

Upvotes: 0

Views: 2725

Answers (1)

marc_s
marc_s

Reputation: 754278

Why OPENXML ?? Seems overly klunky and hard to use - use the XPath/XQuery capabilities in SQL Server !

Try something like this:

DECLARE @input XML = '<Report>
     <Datapoints>
             <Datapoint>10</Datapoint>
             <Datapoint>11</Datapoint>
     </Datapoints>
<Filters>                 
        <Filter Id="5" FilterTypeId="4">            
            <Option2>21</Option2>
            <Option2>22</Option2>             
            <Option2>23</Option2>
        </Filter>        
        <Filter Id="6" FilterTypeId="4">            
            <Option2>21</Option2>
            <Option2>22</Option2>             
            <Option2>23</Option2>
        </Filter>   
    </Filters>           
</Report>'
SELECT
    Filter.value('(@Id)[1]', 'int') AS 'FilterID',
    Filter.value('(@FilterTypeId)[1]', 'int') AS 'FilterTypeID',
    Opt2.value('(.)[1]', 'varchar(50)') AS 'Option2'
FROM 
    @input.nodes('/Report/Filters/Filter') AS Rep(Filter)
CROSS APPLY 
    Filter.nodes('Option2') AS Rep2(Opt2)

This should give you an output something like:

FilterID  FilterTypeID  Option2
   5            4          21
   5            4          22
   5            4          23
   6            4          21
   6            4          22
   6            4          23

Basically, the first use of .nodes() (with the alias of Rep(Filter)) will create a "pseudo-table" of all <Filter> XML tags (one row for each XML tag) and the first two items in the SELECT are grabbing attributes out of each of the <Filter> XML tags.

Then, you need to have a second enumeration, of all <Option2> elements inside each filter - that's what the second .nodes() call is doing - based on the filter XML tag from the first pseudo-table. This then just outputs the XML tag's content as its value.

Upvotes: 2

Related Questions