locked
How to get data correctly from xml RRS feed

  • Question

  • I've an XML like following

    Declare @BathData XML
    SET @BathData='<Batch>
    <Customers>
    <Customer>
    <CustomerId>1</CustomerId>
    <Product>
    <ProductId>10</ProductId>
    <ProductId>11</ProductId>
    </Product>
    </Customer>
    <Customer>
    <CustomerId>2</CustomerId>
    <Product>
    <ProductId>22</ProductId>
    <ProductId>23</ProductId>
    <ProductId>25</ProductId>
    </Product>
    </Customer>

    </Customers>
    </Batch>'

    the result i want is as following

    CusomerId ProductId

    1 10

    1 11

    2 20

    2 23

    2 35

    and i am using following way to sort it out

    SELECT Finaldata.R.value('CustomerId[1]','int') CustomerId,Finaldata.R.value('Product[1]','int') as ProductId
    FROM @BathData.nodes('//Batch/Customers/Customer') as Finaldata (R)

    but obviously its not working a quick response would really help thanks

    Tuesday, May 14, 2013 5:43 AM

Answers

  • Try this:

    DECLARE @BatchData XML
    SET @BatchData = '<Batch>
      <Customers>
        <Customer>
          <CustomerId>1</CustomerId>
          <Product>
            <ProductId>10</ProductId>
            <ProductId>11</ProductId>
          </Product>
        </Customer>
        <Customer>
          <CustomerId>2</CustomerId>
          <Product>
            <ProductId>22</ProductId>
            <ProductId>23</ProductId>
            <ProductId>25</ProductId>
          </Product>
        </Customer>
      </Customers>
    </Batch>'
    
    SELECT 
    	c.c.value('(CustomerId/text())[1]', 'INT') CustomerId,
    	P.c.value('.', 'INT') ProductId
    FROM @BatchData.nodes('/Batch/Customers/Customer') c(c)
    	CROSS APPLY c.c.nodes('Product/ProductId') p(c)

    Tuesday, May 14, 2013 9:08 AM
    Answerer