SQL Server question about how to read XML file from SQL Server

Hi @SQLDev,

Please try the following solution.

DECLARE @INPUTXML XML = 
N'<parts date="2023-02-20T01:01:11.011Z">
	<part>
		<InvoiceDate>01/16/2023</InvoiceDate>
		<VendorID>1</VendorID>
		<Description>Desc1</Description>
		<PartsDetail>
			<line>
				<BranchID>Branch1</BranchID>
				<Amount>4.50</Amount>
			</line>
		</PartsDetail>
	</part>
	<part>
		<InvoiceDate>12/16/2022</InvoiceDate>
		<VendorID>2</VendorID>
		<Description>Desc2</Description>
		<PartsDetail>
			<line>
				<BranchID>Branch1</BranchID>
				<Amount>1.50</Amount>
			</line>
		</PartsDetail>
	</part>
</parts>';

DECLARE @Description VARCHAR(MAX)
   , @BranchID VARCHAR(MAX)
   , @Amount MONEY;

-- count total number of items
DECLARE @i INT
   , @cnt INT = @INPUTXML.value('count(/parts/part)', 'INT');
SELECT @cnt;

-- loop XML item by item
SET @i = 1;
WHILE @i <= @cnt BEGIN

      SELECT @Description = col.value('(Description/text())[1]','VARCHAR(MAX)')
         , @BranchID = col.value('(PartsDetail/line/BranchID/text())[1]','VARCHAR(MAX)')
         , @Amount = col.value('(PartsDetail/line/Amount/text())[1]','MONEY')
      FROM @INPUTXML.nodes('/parts/part[position() = sql:variable("@i")]') AS t(col)

     -- do whatever needed in the loop here

   SET @i += 1;
END

SQL Snippet

DECLARE @INPUTXML XML = (SELECT *
FROM OPENROWSET (BULK N'e:\Temp\input.xml', SINGLE_BLOB) AS x);

UPDATE

DECLARE @INPUTXML XML = (SELECT *
FROM OPENROWSET (BULK N'e:\Temp\input.xml', SINGLE_BLOB) AS x);

DECLARE @Description VARCHAR(MAX)
   , @BranchID VARCHAR(MAX)
   , @Amount MONEY;

-- count total number of items
DECLARE @i INT
   , @cnt INT = @INPUTXML.value('count(/parts/part/PartsDetail/line)', 'INT');

DECLARE @xml XML = @INPUTXML.query('
	for $x in /parts/part/PartsDetail/line
	return <line>{$x/../../Description}{$x/*}</line>
');

-- loop XML item by item
SET @i = 1;
WHILE @i <= @cnt BEGIN
      SELECT @Description = col.value('(Description/text())[1]','VARCHAR(MAX)')
         , @BranchID = col.value('(BranchID/text())[1]','VARCHAR(MAX)')
         , @Amount = col.value('(Amount/text())[1]','MONEY')
      FROM @xml.nodes('/line[position() = sql:variable("@i")]') AS t(col)

     -- do whatever needed in the loop here, call SP, etc.
	 SELECT @i AS i
	     , @Description AS Desciption
         , @BranchID AS BranchID
         , @Amount AS Amount

   SET @i += 1;
END

Thanks so much. Just came to know that there can be multiple lines within PartsDetail. I guess I need to have another loop within PartsDetail? Thanks again!!

<parts date="2023-02-20T01:01:11.011Z">
	<part>
		<InvoiceDate>01/16/2023</InvoiceDate>
		<VendorID>1</VendorID>
		<Description>Desc1</Description>
		<PartsDetail>
			<line>
				<BranchID>Branch1</BranchID>
				<Amount>4.50</Amount>
			</line>
			<line>
				<BranchID>Branch2</BranchID>
				<Amount>5.50</Amount>
			</line>
			<line>
				<BranchID>Branch3</BranchID>
				<Amount>6.50</Amount>
			</line>
		</PartsDetail>
	</part>
	<part>
		<InvoiceDate>12/16/2022</InvoiceDate>
		<VendorID>2</VendorID>
		<Description>Desc2</Description>
		<PartsDetail>
			<line>
				<BranchID>Branch1</BranchID>
				<Amount>1.50</Amount>
			</line>
		</PartsDetail>
	</part>
</

Thanks so much! Is there anyway I can call the XML file from a folder location instead of pasting the entire XML file in the script? The XML file is going to be big one. If so, what changes you will suggest to your script? Thanks again!!