Importing from XML

Question

DECLARE @index int 
DECLARE @XMLdoc varchar(8000)
SET @XMLdoc ='<Sales>
<Company>101</Company>
<Warehouse>S001</Warehouse>
<InvoiceNumber>1001</InvoiceNumber>
<Amount>1000.0000</Amount>
<CreateDate>20180527</CreateDate>
<Company>101</Company>
<Warehouse>S002</Warehouse>
<InvoiceNumber>1002</InvoiceNumber>
<Amount>2000.0000</Amount>
<CreateDate>20180527</CreateDate>
</Sales>'
EXEC sp_xml_preparedocument @index OUTPUT, @XMLdoc
SELECT * FROM OPENXML (@index, 'Sales',2)
WITH (Company int,Warehouse nvarchar(20),InvoiceNumber nvarchar(40),Amount 
nvarchar(10),CreateDate nvarchar(20))
EXEC sp_xml_removedocument @index

The above query returning only single row even though the XML formatted data contains two row data.I want both rows as result of this query.

Answer 1

First of all: FROM OPENXML together with the corresponding SPs to prepare and to remove a document is outdated and should not be used any more. Rather use the native XML type's mehtods.

Assuming that two rows mean two <Sales> blocks I'd like to suggest this:

DECLARE @XMLdoc XML=
N'<Sales>
  <Company>101</Company>
  <Warehouse>S001</Warehouse>
  <InvoiceNumber>1001</InvoiceNumber>
  <Amount>1000.0000</Amount>
  <CreateDate>20180527</CreateDate>
  <Company>101</Company>
  <Warehouse>S002</Warehouse>
  <InvoiceNumber>1002</InvoiceNumber>
  <Amount>2000.0000</Amount>
  <CreateDate>20180527</CreateDate>
</Sales>
<Sales>
  <Company>999</Company>
  <Warehouse>999</Warehouse>
  <InvoiceNumber>999</InvoiceNumber>
  <Amount>1000.0000</Amount>
  <CreateDate>20180527</CreateDate>
  <Company>101</Company>
  <Warehouse>S002</Warehouse>
  <InvoiceNumber>1002</InvoiceNumber>
  <Amount>2000.0000</Amount>
  <CreateDate>20180527</CreateDate>
</Sales>';

SELECT s.value(N'(Company/text())[1]',N'int') AS Company
      ,s.value(N'(Warehouse/text())[1]',N'nvarchar(max)') AS Warehouse
      ,s.value(N'(InvoiceNumber/text())[1]',N'int') AS InvoiceNumber
      ,s.value(N'(Amount/text())[1]',N'decimal(14,6)') AS Amount
      --and so on
FROM @XMLdoc.nodes(N'/Sales') AS A(s) 

UPDATE (Please avoid follow up questions!)

Your comment asking for attributes points to something like this:

SELECT 101 AS [@Company]
      ,Warehouse
      --more elements
FROM SomeWhere
FOR XML PATH('Idocs'),ROOT('Sales');