actually i want to parse xml to sql using following xml node i want xml input parameter then got in Table.
<root>
<Header>
<RqID>20220210125441</RqID>
</Header>
<Item>
<element>
<FromCrcy>JPY</FromCrcy>
<Rate>0.00886</Rate>
<RatioFrom>0</RatioFrom>
<RatioTo>0</RatioTo>
<ToCrcy>EUR</ToCrcy>
<ValidFrom>26.07.2010</ValidFrom>
</element>
</Item>
<ResponseStatus>
<StatusCode>I000000</StatusCode>
<StatusDesc>Success</StatusDesc>
</ResponseStatus>
</root>
following script i am using to get xml to table in sql server but getting as null so please solve it.
Set @StrMessage = ''
Declare @intPointer int
Exec sp_xml_preparedocument @intPointer OUTPUT, @MessageXML
--PRINT CONVERT(NVARCHAR(MAX),@MessageXML)
Create Table #Currency_bas
(
RowID int Identity, RqID nvarchar(50), FromCrcy nvarchar(3), Rate Decimal(13,3), RatioFrom int, RatioTo int
, ToCrcy nvarchar(3), ValidFrom date,StatusCode nvarchar(50),StatusDesc nvarchar(50)
)
Insert Into #Currency_bas
(
--RqID,
--StatusCode,StatusDesc --, --
FromCrcy, Rate, RatioFrom, RatioTo
, ToCrcy, ValidFrom
)
Select
--RqID,StatusCode,StatusDesc --,
FromCrcy, Rate, RatioFrom, RatioTo
,ToCrcy, ValidFrom
From OPENXML(@intPointer, '/root',2)
WITH
(
--RqID nvarchar(50),
--StatusCode nvarchar(50),
--StatusDesc nvarchar(50)
FromCrcy nvarchar(3),
Rate Decimal(13,3),
RatioFrom int,
RatioTo int,
ToCrcy nvarchar(3),
ValidFrom date
)
SELECT FromCrcy, Rate, RatioFrom, RatioTo
,ToCrcy, ValidFrom FROM #Currency_bas
how to parse this xml in sql table so please give me example to solve this.
CodePudding user response:
Microsoft proprietary OPENXML
and its companions sp_xml_preparedocument
and sp_xml_removedocument
are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases.
Also, folks are usually forget to call the sp_xml_removedocument
. And it is causing a memory leak.
Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery.
SQL
DECLARE @MessageXML XML =
N'<root>
<Header>
<RqID>20220210125441</RqID>
</Header>
<Item>
<element>
<FromCrcy>JPY</FromCrcy>
<Rate>0.00886</Rate>
<RatioFrom>0</RatioFrom>
<RatioTo>0</RatioTo>
<ToCrcy>EUR</ToCrcy>
<ValidFrom>26.07.2010</ValidFrom>
</element>
</Item>
<ResponseStatus>
<StatusCode>I000000</StatusCode>
<StatusDesc>Success</StatusDesc>
</ResponseStatus>
</root>';
SELECT RqID = @MessageXML.value('(/root/Header/RqID/text())[1]', 'VARCHAR(50)')
, StatusCode = @MessageXML.value('(/root/ResponseStatus/StatusCode/text())[1]', 'VARCHAR(50)')
, StatusDesc = @MessageXML.value('(/root/ResponseStatus/StatusDesc/text())[1]', 'VARCHAR(50)')
, FromCrcy = c.value('(FromCrcy/text())[1]', 'CHAR(3)')
, Rate = c.value('(Rate/text())[1]', 'DECIMAL(13,5)')
, RatioFrom = c.value('(RatioFrom/text())[1]', 'INT')
, RatioTo = c.value('(RatioTo/text())[1]', 'INT')
, ToCrcy = c.value('(ToCrcy/text())[1]', 'CHAR(3)')
, ValidFrom = TRY_CONVERT(DATE, c.value('(ValidFrom/text())[1]', 'VARCHAR(10)'), 104)
FROM @MessageXML.nodes('/root/Item/element') AS t(c);
Output
---------------- ------------ ------------ ---------- --------- ----------- --------- -------- ------------
| RqID | StatusCode | StatusDesc | FromCrcy | Rate | RatioFrom | RatioTo | ToCrcy | ValidFrom |
---------------- ------------ ------------ ---------- --------- ----------- --------- -------- ------------
| 20220210125441 | I000000 | Success | JPY | 0.00886 | 0 | 0 | EUR | 2010-07-26 |
---------------- ------------ ------------ ---------- --------- ----------- --------- -------- ------------
CodePudding user response:
If you inspect your data you'll see it doesn't start at root
it starts at root/Item/element
Also note the extra date conversion step.
An example of the syntax you need to get RqID
and Status
is here
DECLARE @MessageXML VARCHAR(8000)
SET @MessageXML = '
<root>
<Header><RqID>20220210125441</RqID></Header>
<Item>
<element>
<FromCrcy>JPY</FromCrcy>
<Rate>0.00886</Rate>
<RatioFrom>0</RatioFrom>
<RatioTo>0</RatioTo>
<ToCrcy>EUR</ToCrcy>
<ValidFrom>26.07.2010</ValidFrom>
</element>
</Item>
<ResponseStatus>
<StatusCode>I000000</StatusCode>
<StatusDesc>Success</StatusDesc>
</ResponseStatus>
</root>
'
Declare @intPointer int
Exec sp_xml_preparedocument @intPointer OUTPUT, @MessageXML
Select
RqID,
StatusCode,StatusDesc,
FromCrcy, Rate, RatioFrom, RatioTo
,ToCrcy, CONVERT(DATE,ValidFrom,104) as ValidFrom
From OPENXML(@intPointer, '/root/Item/element',2)
WITH
(
RqID nvarchar(50) '../../Header/RqID',
StatusCode nvarchar(50) '../../ResponseStatus/StatusCode',
StatusDesc nvarchar(50) '../../ResponseStatus/StatusDesc',
FromCrcy nvarchar(3),
Rate Decimal(13,3),
RatioFrom int,
RatioTo int,
ToCrcy nvarchar(3),
ValidFrom varchar(100)
)