November 3, 2016 at 1:27 am
Hi All,
I will get XML input from the UI and need to insert the same into one of my SQL Server tables and below is the XML format. Can anyone please help me with the query.
<Header>
<BatchGUID>b75f8f79-9c37-444c-af4a-714e42197f02</BatchGUID>
<MsgType>703</MsgType>
<CompID>098</CompID>
<BranchID>001</BranchID>
<TotalRecordsInSeq>6</TotalRecordsInSeq>
<SeqNo>1</SeqNo>
<TotalSeqNo>1</TotalSeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-11-02</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
</Header>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312121</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF11</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>12343</Terminal>
<OrderNo>877313</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>9888</TRSNo>
<StkCode>BSB</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>1</TransCode>
<Qty>100000</Qty>
<OrderType>00</OrderType>
<Price>1.053</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>021</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312341</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF12</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>125643</Terminal>
<OrderNo>879943</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>93453</TRSNo>
<StkCode>US</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>2</TransCode>
<Qty>15000</Qty>
<OrderType>00</OrderType>
<Price>2.034</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>023</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
Below is my query which i'm not able to insert the record.
;WITH CTE AS
(
SELECT
ID = XTbl.value('(ID)[1]', 'uniqueidentifier'),
BatchGUID = XTbl.value('(BatchGUID)[1]', 'uniqueidentifier'),
MsgType = XTbl.value('(MsgType)[1]', 'nvarchar(50)'),
CompID = XTbl.value('(CompID)[1]', 'nvarchar(12)'),
BranchID = XTbl.value('(BranchID)[1]', 'nvarchar(12)'),
TotalRecords = XTbl.value('(TotalRecords)[1]', 'int'),
[Status] = XTbl.value('(Status)[1]', 'nvarchar(20)'),
MessageBody = XTbl.value('(/Message)[1]', 'nvarchar(max)'),
CreatedBy = XTbl.value('(CreatedBy)[1]', 'nvarchar(50)'),
ProcessDT = XTbl.value('(ProcessDT)[1]', 'date'),
[TimeStamp] = XTbl.value('(TimeStamp)[1]', 'datetime')
FROM
@xml_input.nodes('/Header') AS XD(XTbl)
)
INSERT INTO
dbo.ReceivedRawMessages(ID, BatchGUID, MsgType, CompID, BranchID, TotalRecords, [Status], MessageBody, CreatedBy, ProcessDT, [TimeStamp])
SELECT
ID,
BatchGUID,
MsgType,
CompID,
BranchID,
TotalRecords,
[Status],
MessageBody,
CreatedBy,
ProcessDT,
[TimeStamp]
FROM
CTE
November 3, 2016 at 1:56 am
This XML is not well formed as there are multiple root nodes, is this the actual XML?
😎
The CTE in your code will only retrieve values from the Header part of the XML, is that the correct logic?
<Header></Header>
<Message></Message>
<Message></Message>
November 3, 2016 at 2:00 am
Yes, it is the format i get from UI, May be i need to have 2 more root elements, one to include all the messages and th other to hold both the header and messages if i'm not wrong.
Any solution for this xml?
November 3, 2016 at 2:10 am
How about if my XML is like below? Please suggest.
<MessageBody>
<Header>
<BatchGUID>b75f8f79-9c37-444c-af4a-714e42197f02</BatchGUID>
<MsgType>703</MsgType>
<CompID>098</CompID>
<BranchID>001</BranchID>
<TotalRecordsInSeq>6</TotalRecordsInSeq>
<SeqNo>1</SeqNo>
<TotalSeqNo>1</TotalSeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-11-03</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
</Header>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12442341</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF13</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>125643</Terminal>
<OrderNo>199123</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>93453</TRSNo>
<StkCode>SMPH</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>1</TransCode>
<Qty>450000</Qty>
<OrderType>00</OrderType>
<Price>1.934</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>023</CPartyID>
<CDSNo>1253233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
</MessageBody>
November 3, 2016 at 2:32 am
Shredding xml is not exactly the best perfprming operation in SQL. Rather get the UI to shred the XML and pass you a TVP (table valued parameter).
If you need a relational structure, the UI can pass multiple TVPs and your proc can join them.
November 3, 2016 at 2:32 am
The XML data type in SQL Server can easily handle multiple root nodes which is somewhat an exception to the rule, my concern was that normally this would break or error in other environments.
😎
Here is a quick suggestion that could work, it is missing both the ID and the Status value as those cannot be found in the XML.
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<MessageBody>
<Header>
<BatchGUID>b75f8f79-9c37-444c-af4a-714e42197f02</BatchGUID>
<MsgType>703</MsgType>
<CompID>098</CompID>
<BranchID>001</BranchID>
<TotalRecordsInSeq>6</TotalRecordsInSeq>
<SeqNo>1</SeqNo>
<TotalSeqNo>1</TotalSeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-11-02</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
</Header>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312121</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF11</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>12343</Terminal>
<OrderNo>877313</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>9888</TRSNo>
<StkCode>BSB</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>1</TransCode>
<Qty>100000</Qty>
<OrderType>00</OrderType>
<Price>1.053</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>021</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312341</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF12</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>125643</Terminal>
<OrderNo>879943</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>93453</TRSNo>
<StkCode>US</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>2</TransCode>
<Qty>15000</Qty>
<OrderType>00</OrderType>
<Price>2.034</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>023</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
</MessageBody>';
;WITH HEADER_CTE AS
(
SELECT
ID = XTbl.value('(ID/text())[1]' , 'uniqueidentifier')
,BatchGUID = XTbl.value('(BatchGUID/text())[1]' , 'uniqueidentifier')
,MsgType = XTbl.value('(MsgType/text())[1]' , 'nvarchar(50)')
,CompID = XTbl.value('(CompID/text())[1]' , 'nvarchar(12)')
,BranchID = XTbl.value('(BranchID/text())[1]' , 'nvarchar(12)')
,TotalRecords = XTbl.value('(TotalRecords/text())[1]' , 'int')
,[Status] = XTbl.value('(Status/text())[1]' , 'nvarchar(20)')
,MessageBody = MSG.DATA.query('(.)')
,CreatedBy = XTbl.value('(CreatedBy/text())[1]' , 'nvarchar(50)')
,ProcessDT = XTbl.value('(ProcessDT/text())[1]' , 'date')
,[TimeStamp] = XTbl.value('(TimeStamp/text())[1]' , 'datetime')
FROM @TXML.nodes('/MessageBody/Header ') AS XD(XTbl)
CROSS APPLY @TXML.nodes('/MessageBody/Message') AS MSG(DATA)
)
--INSERT INTO
-- dbo.ReceivedRawMessages(ID, BatchGUID, MsgType, CompID, BranchID, TotalRecords, [Status], MessageBody, CreatedBy, ProcessDT, [TimeStamp])
SELECT
HC.ID
,HC.BatchGUID
,HC.MsgType
,HC.CompID
,HC.BranchID
,HC.TotalRecords
,HC.[Status]
,HC.MessageBody
,HC.CreatedBy
,HC.ProcessDT
,HC.[TimeStamp]
FROM
HEADER_CTE HC;
Output
MsgType CompID BranchID TotalRecords Status MessageBody CreatedBy ProcessDT TimeStamp
--------- ------- --------- ------------ -------- ---------------------------------------------------------------------------- ----------- ---------- ----------
703 098 001 6 NULL <Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">...</Message> QUEUE 2016-11-02 NULL
703 098 001 6 NULL <Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">...</Message> QUEUE 2016-11-02 NULL
November 3, 2016 at 3:00 am
DesNorton (11/3/2016)
Shredding xml is not exactly the best perfprming operation in SQL. Rather get the UI to shred the XML and pass you a TVP (table valued parameter).If you need a relational structure, the UI can pass multiple TVPs and your proc can join them.
This isn't entirely correct, the XML operations in SQL Server can be extremely fast if correctly implemented, just as they can be slow if not implemented correctly. As an example, the text() function I added in the code earlier speeds the shredding up by a considerable margin. The internal XML Reader used is an implementation of the .Net stream reader which is very efficient and it would be hard to beat that in the UI. Further, there is added complexity in the UI/Server communication by sending several parameters instead of a single XML parameter, wouldn't suggest this unless there was a definite problem to be fixed.
😎
November 3, 2016 at 3:18 am
Eirikur Eiriksson (11/3/2016)
DesNorton (11/3/2016)
Shredding xml is not exactly the best perfprming operation in SQL. Rather get the UI to shred the XML and pass you a TVP (table valued parameter).If you need a relational structure, the UI can pass multiple TVPs and your proc can join them.
This isn't entirely correct, the XML operations in SQL Server can be extremely fast if correctly implemented, just as they can be slow if not implemented correctly. As an example, the text() function I added in the code earlier speeds the shredding up by a considerable margin. The internal XML Reader used is an implementation of the .Net stream reader which is very efficient and it would be hard to beat that in the UI. Further, there is added complexity in the UI/Server communication by sending several parameters instead of a single XML parameter, wouldn't suggest this unless there was a definite problem to be fixed.
😎
Thank you Eirikur.
November 3, 2016 at 4:00 am
Hi,
Thanks for the query!
I'm able to select all the columns from XML, But when i try to run the insert query getting below error.
Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
PS : MessageBody column in my table is of type nvarchar(max)
November 3, 2016 at 4:09 am
User7766 (11/3/2016)
Hi,Thanks for the query!
I'm able to select all the columns from XML, But when i try to run the insert query getting below error.
Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
PS : MessageBody column in my table is of type nvarchar(max)
Strongly suggest you change the data type to XML, it is very inefficient to store XML as a character data type.
😎
November 3, 2016 at 4:10 am
Added this line to make it work, somewhere i'm missing the casting
,MessageBody = CAST(MSG.DATA.query('(.)') AS nvarchar(MAX))
Thanks alot!
November 3, 2016 at 4:30 am
User7766 (11/3/2016)
Added this line to make it work, somewhere i'm missing the casting,MessageBody = CAST(MSG.DATA.query('(.)') AS nvarchar(MAX))
Thanks alot!
Why would you ever want to store the XML as an NVARCHAR(MAX)? It almost doubles the size of the data and any operations on the data will require a conversion back to XML. The XML data type storage format is so efficient it hardly pays of compressing an XML string.
😎
Using the previously posted XML snipped
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<MessageBody>
<Header>
<BatchGUID>b75f8f79-9c37-444c-af4a-714e42197f02</BatchGUID>
<MsgType>703</MsgType>
<CompID>098</CompID>
<BranchID>001</BranchID>
<TotalRecordsInSeq>6</TotalRecordsInSeq>
<SeqNo>1</SeqNo>
<TotalSeqNo>1</TotalSeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-11-02</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
</Header>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312121</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF11</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>12343</Terminal>
<OrderNo>877313</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>9888</TRSNo>
<StkCode>BSB</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>1</TransCode>
<Qty>100000</Qty>
<OrderType>00</OrderType>
<Price>1.053</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>021</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
<Message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BatchGUID>B75F8F79-9C37-444C-AF4A-714E42197F02</BatchGUID>
<MsgType>703</MsgType>
<SeqNo>12312341</SeqNo>
<TotalRecords>6</TotalRecords>
<ProcessDT>2016-09-28</ProcessDT>
<CreatedBy>QUEUE</CreatedBy>
<ClntAcctNo>BEF12</ClntAcctNo>
<XchgID>NYSE</XchgID>
<CountryID>US</CountryID>
<DealerID>US1</DealerID>
<BranchID>001</BranchID>
<Terminal>125643</Terminal>
<OrderNo>879943</OrderNo>
<ContractDate>20160923</ContractDate>
<TRSNo>93453</TRSNo>
<StkCode>US</StkCode>
<SettlementCode>1</SettlementCode>
<TransCode>2</TransCode>
<Qty>15000</Qty>
<OrderType>00</OrderType>
<Price>2.034</Price>
<MatchedTime>12111111</MatchedTime>
<CPartyID>023</CPartyID>
<CDSNo>1233233</CDSNo>
<CompID>098</CompID>
<OnlineInd>N</OnlineInd>
<CcyID>USD</CcyID>
<TCcyID>USD</TCcyID>
</Message>
</MessageBody>';
SELECT
DATALENGTH(@TXML) AS XML_DATALENGTH
,DATALENGTH(CONVERT(NVARCHAR(MAX),@TXML,0)) AS NVARCHAR_DATALENGTH
;
Output
XML_DATALENGTH NVARCHAR_DATALENGTH
-------------- --------------------
2011 3900
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply