I'm using API that gives the following response:

<events><item0><PermitId>705130</PermitId><UserID>389230</UserID><CarID>348729</CarID><StartDate>2022-11-22</StartDate><EndDate>2022-11-22</EndDate><RequestDate>2022-02-22</RequestDate><PermitName>Leisure Visit</PermitName><ExpectedTime>13</ExpectedTime><Companions>6</Companions><StatusID>1</StatusID><Status>Active</Status><City>Makkah</City></item0>

<item1><PermitId>846926</PermitId><UserID>281556</UserID><CarID>403407</CarID><StartDate>2022-10-23</StartDate><EndDate>2022-12-31</EndDate><RequestDate>2022-10-23</RequestDate><PermitName>Yearly Permit</PermitName><ExpectedTime>0</ExpectedTime><Companions>0</Companions><StatusID>1</StatusID><Status>Active</Status><City>Jeddah</City></item1></events>

How can I insert it into Sql table using sql?

I have tried the way but it didn't work for me

SELECT *
FROM
OPENXML(@xml,'//*')
WITH (  
[PermitId] int,
[UserID] int,
[CarID] nvarchar(50),
[StartDate] date,
[EndDate] date,
[RequestDate] date,
[PermitName] nvarchar(100),
[ExpectedTime] int,
[Companions] int,
[StatusID] int,
[Status] nvarchar(100),
[City] nvarchar(100)); 

 

CodePudding user response：

You had it almost right.

But you need to cappture all items so you need '/events/*'

And also vital the last parameter 2, which takes the sublelemts as rows

DECLARE @idoc INT, @doc VARCHAR(1000);  
SET @doc ='  
<events>
  <item0>
  <PermitId>705130</PermitId>
  <UserID>389230</UserID>
  <CarID>348729</CarID>
  <StartDate>2022-11-22</StartDate>
  <EndDate>2022-11-22</EndDate>
  <RequestDate>2022-02-22</RequestDate>
  <PermitName>Leisure Visit</PermitName>
  <ExpectedTime>13</ExpectedTime>
  <Companions>6</Companions>
  <StatusID>1</StatusID>
  <Status>Active</Status>
  <City>Makkah</City>
  </item0>

<item1><PermitId>846926</PermitId><UserID>281556</UserID><CarID>403407</CarID><StartDate>2022-10-23</StartDate><EndDate>2022-12-31</EndDate><RequestDate>2022-10-23</RequestDate><PermitName>Yearly Permit</PermitName><ExpectedTime>0</ExpectedTime><Companions>0</Companions><StatusID>1</StatusID><Status>Active</Status><City>Jeddah</City></item1></events>

';  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;  
-- Execute a SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/events/*',2)  
            WITH ([PermitId] int ,
  [UserID] int,
[CarID] nvarchar(50),
[StartDate] date,
[EndDate] date,
[RequestDate] date,
[PermitName] nvarchar(100),
[ExpectedTime] int,
[Companions] int,
[StatusID] int,
[Status] nvarchar(100),
[City] nvarchar(100));  

fiddle