locked
How to get data correctly from xml RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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