How do I select all or multiple rows from a typed xml variable?

• rehman-615909

  Ten Centuries

  Points: 1360

  More actions

  April 2, 2014 at 10:23 am

  #287185

  I am using xml schema that is like this:

  <DetailRows>

  <DetailRow>

  <MonthNumber></MonthNumber>

  <Amount></Amount>

  </DetailRow>

  </DetailRows>

  If my variable contains following xml document as un-typed xml

  <DetailRows>

  <DetailRow>

  <MonthNumber>1</MonthNumber>

  <Amount>1000</Amount>

  </DetailRow>

  <DetailRow>

  <MonthNumber>11</MonthNumber>

  <Amount>300</Amount>

  </DetailRow>

  <DetailRow>

  <MonthNumber>5</MonthNumber>

  <Amount>5000</Amount>

  </DetailRow>

  </DetailRows>

  then I can use following query to select all the rows:

  SELECTx.MonthNumber, x.Amount

  FROMOPENXML(@hDoc, '/DetailRows/DetailRow', 1)

  WITH(MonthNumber int 'MonthNumber', Amount decimal(10,2) 'Amount'

  ) x

  However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc?

  I am using SS2K5.

  Thanks.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  April 2, 2014 at 1:15 pm

  #1702604

  This should work on 2K5

  DECLARE @XML XML = N'<DetailRows>

  <DetailRow>

  <MonthNumber>1</MonthNumber>

  <Amount>1000</Amount>

  </DetailRow>

  <DetailRow>

  <MonthNumber>11</MonthNumber>

  <Amount>300</Amount>

  </DetailRow>

  <DetailRow>

  <MonthNumber>5</MonthNumber>

  <Amount>5000</Amount>

  </DetailRow>

  </DetailRows>'

  SELECT

  DT.NO.value('MonthNumber[1]','INT') AS MonthNumber

  ,DT.NO.value('Amount[1]','DECIMAL(18,5)') AS Amount

  --,DT.NO.query('(.)')

  FROM @XML.nodes('DetailRows/DetailRow') AS DT(NO)

  Output:

  MonthNumber Amount

  ----------- -------------

  1 1000.00000

  11 300.00000

  5 5000.00000