June 12, 2018 at 1:19 pm
The XML in the attached file is the XMLTEXT field in my CDRecords table.
What code would i use to cross apply the _CDD nodes of the _Detail node to the CDRecords table, and put the values of CDD04 and LIN04 into the CDD04 and LIN04 columns respectively.
June 12, 2018 at 1:27 pm
Jackie Lowery - Tuesday, June 12, 2018 1:19 PMI need to cross apply the _CDD nodes of the _Detail node, and put the values of CDD04 and LIN04 into the CDD04 and LIN04 columns respectively.
This is a requirement. What is your question?
Please also provide your desired output, based on the sample data.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 12, 2018 at 1:35 pm
Phil Parkin - Tuesday, June 12, 2018 1:27 PMJackie Lowery - Tuesday, June 12, 2018 1:19 PMI need to cross apply the _CDD nodes of the _Detail node, and put the values of CDD04 and LIN04 into the CDD04 and LIN04 columns respectively.This is a requirement. What is your question?
Please also provide your desired output, based on the sample data.
Updated question. Make sense now?
June 12, 2018 at 11:21 pm
If I understand the question correctly, then this could be a solution.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<_DETAIL>
<_CDD docLine="16">
<CDD docLine="16">
<CDD01 _="F1" />
<CDD02 _="C" />
<CDD03 _="1" />
<CDD04 _="525" />
<CDD05 />
<CDD06 />
<CDD07 _="1" />
<CDD08 _="EA" />
<CDD09 />
<CDD10 _="UCP" />
<CDD11 _="5.25" />
</CDD>
<LIN docLine="17">
<LIN01 />
<LIN02 _="IN" />
<LIN03 _="033092437" />
<LIN04 _="UI" />
<LIN05 _="4852695321" />
<LIN06 _="VN" />
<LIN07 _="95321" />
</LIN>
<N9 docLine="18">
<N901 _="6O" />
<N902 _="0094" />
</N9>
</_CDD>
<_CDD docLine="19">
<CDD docLine="19">
<CDD01 _="F1" />
<CDD02 _="C" />
<CDD03 _="2" />
<CDD04 _="200" />
<CDD05 />
<CDD06 />
<CDD07 _="1" />
<CDD08 _="EA" />
<CDD09 />
<CDD10 _="UCP" />
<CDD11 _="2" />
</CDD>
<LIN docLine="20">
<LIN01 />
<LIN02 _="IN" />
<LIN03 _="034236085" />
<LIN04 _="UI" />
<LIN05 _="4852605781" />
<LIN06 _="VN" />
<LIN07 _="5781" />
</LIN>
<N9 docLine="21">
<N901 _="6O" />
<N902 _="0094" />
</N9>
</_CDD>
</_DETAIL>';
SELECT
CDD.DATA.value('@docLine','INT') AS docLine
,SCDD.DATA.value('@_','INT') AS CDD04
,SLIN.DATA.value('@_','VARCHAR(10)') AS LIN04
FROM @TXML.nodes('_DETAIL/_CDD') CDD(DATA)
CROSS APPLY CDD.DATA.nodes('CDD/CDD04') SCDD(DATA)
CROSS APPLY CDD.DATA.nodes('LIN/LIN04') SLIN(DATA);
Output
docLine CDD04 LIN04
-------- ------ ------
16 525 UI
19 200 UI
June 13, 2018 at 5:12 pm
Eirikur Eiriksson - Tuesday, June 12, 2018 11:21 PMIf I understand the question correctly, then this could be a solution.
😎
USE TEEST;
GO
SET NOCOUNT ON;DECLARE @TXML XML = '<_DETAIL>
<_CDD docLine="16">
<CDD docLine="16">
<CDD01 _="F1" />
<CDD02 _="C" />
<CDD03 _="1" />
<CDD04 _="525" />
<CDD05 />
<CDD06 />
<CDD07 _="1" />
<CDD08 _="EA" />
<CDD09 />
<CDD10 _="UCP" />
<CDD11 _="5.25" />
</CDD>
<LIN docLine="17">
<LIN01 />
<LIN02 _="IN" />
<LIN03 _="033092437" />
<LIN04 _="UI" />
<LIN05 _="4852695321" />
<LIN06 _="VN" />
<LIN07 _="95321" />
</LIN>
<N9 docLine="18">
<N901 _="6O" />
<N902 _="0094" />
</N9>
</_CDD>
<_CDD docLine="19">
<CDD docLine="19">
<CDD01 _="F1" />
<CDD02 _="C" />
<CDD03 _="2" />
<CDD04 _="200" />
<CDD05 />
<CDD06 />
<CDD07 _="1" />
<CDD08 _="EA" />
<CDD09 />
<CDD10 _="UCP" />
<CDD11 _="2" />
</CDD>
<LIN docLine="20">
<LIN01 />
<LIN02 _="IN" />
<LIN03 _="034236085" />
<LIN04 _="UI" />
<LIN05 _="4852605781" />
<LIN06 _="VN" />
<LIN07 _="5781" />
</LIN>
<N9 docLine="21">
<N901 _="6O" />
<N902 _="0094" />
</N9>
</_CDD>
</_DETAIL>';SELECT
CDD.DATA.value('@docLine','INT') AS docLine
,SCDD.DATA.value('@_','INT') AS CDD04
,SLIN.DATA.value('@_','VARCHAR(10)') AS LIN04
FROM @TXML.nodes('_DETAIL/_CDD') CDD(DATA)
CROSS APPLY CDD.DATA.nodes('CDD/CDD04') SCDD(DATA)
CROSS APPLY CDD.DATA.nodes('LIN/LIN04') SLIN(DATA);Output
docLine CDD04 LIN04
-------- ------ ------
16 525 UI
19 200 UI
That code runs really fast. I've integrated what you were doing into my actual code here:
select edxd.Misc_ID
, [CD Flag] = cdxml.value('(//_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD/BCD05/ @_)[1]','char(1)')
, [Total Amt] = cdxml.value('(//_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD/BCD04/ @_)[1]','decimal')
, [Line Amt] = CDD4.DATA.value('(@_)','varchar(30)')
, [Cus Item Number] = LIN3.DATA.value('(@_)','char(30)')
, [Reason Cd] = CDD1.DATA.value('(@_)','char(3)')
from XMLDoc edxd
cross apply (select cast(XMLText as XML) ) xmltext(cdxml)
cross apply
cdxml.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') CDD(DATA)
OUTER APPLY CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY CDD.DATA.nodes('LIN/LIN03') LIN3(DATA)
OUTER APPLY CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
WHERE edxd.Misc_ID = 249331
order by Misc_ID desc
My problem seems to be the initial cross apply of the XMLText field b/c it's not XML in the table, it's ntext. I've attached the execution plan. There's sooooo many nested loops. 🙁
Thanks again for the help guys.
June 14, 2018 at 7:18 am
Can anyone help with XML code that will return results with better speed? In the example from the post above, a single record with XML that contains 100 CDD and LIN nodes takes about 6 sec to run. I hope i can get a result that is much faster. I've attached a much more real life example:
June 14, 2018 at 11:15 am
Jackie Lowery - Thursday, June 14, 2018 7:18 AMCan anyone help with XML code that will return results with better speed? In the example from the post above, a single record with XML that contains 100 CDD and LIN nodes takes about 6 sec to run. I hope i can get a result that is much faster. I've attached a much more real life example:
I wouldn't count on it... the problem is that XML is not exactly trivial to parse. It's never been a good idea to start loading up XML with hundreds or thousands of values, as it's original intent was just to provide hierarchy ability for small sets of data, and as database engines are good at hierarchical structures, that's a theoretical match, but only when the data is actually stored hierarchically in tables properly designed to handle it. XML is just text, so the parsing of that text is the grief. That's why it's almost always a very bad idea to use it for any volume of data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2018 at 8:24 am
sgmunson - Thursday, June 14, 2018 11:15 AMJackie Lowery - Thursday, June 14, 2018 7:18 AMCan anyone help with XML code that will return results with better speed? In the example from the post above, a single record with XML that contains 100 CDD and LIN nodes takes about 6 sec to run. I hope i can get a result that is much faster. I've attached a much more real life example:I wouldn't count on it... the problem is that XML is not exactly trivial to parse. It's never been a good idea to start loading up XML with hundreds or thousands of values, as it's original intent was just to provide hierarchy ability for small sets of data, and as database engines are good at hierarchical structures, that's a theoretical match, but only when the data is actually stored hierarchically in tables properly designed to handle it. XML is just text, so the parsing of that text is the grief. That's why it's almost always a very bad idea to use it for any volume of data.
right you are. unfortunately the EDI solution sticks all of our documents in a table in that format. 🙁
June 17, 2018 at 6:09 am
Jackie Lowery - Thursday, June 14, 2018 7:18 AMCan anyone help with XML code that will return results with better speed? In the example from the post above, a single record with XML that contains 100 CDD and LIN nodes takes about 6 sec to run. I hope i can get a result that is much faster. I've attached a much more real life example:
Few things that can be improved, specially getting rid of the traversing (//) in the two BCD value clauses.
😎
This performs much better (using the sample you provided in the attachment)
,XML_CONVERT AS
(
SELECT
SD.miscID
,CONVERT(XML,SD.xmltext,0) AS TXML
FROM cteCDD SD
)
SELECT
XC.miscID
,BCD.[DATA].value('(BCD05/@_)[1]','char(1)') AS BCD05
,BCD.[DATA].value('(BCD04/@_)[1]','NUMERIC(15,5)') AS BCD05
,CDD4.DATA.value('@_','INT') AS CDD04
,LIN3.DATA.value('@_','VARCHAR(10)') AS LIN04
,CDD1.DATA.value('@_','VARCHAR(10)') AS CDD01
FROM XML_CONVERT XC
CROSS APPLY XC.TXML.nodes('_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') CDD(DATA)
CROSS APPLY XC.TXML.nodes('_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
OUTER APPLY CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
OUTER APPLY CDD.DATA.nodes('LIN/LIN03') LIN3(DATA);
July 6, 2018 at 7:42 am
Sorry for the late response on this. Project got put on the backburner for a couple weeks. So, to catch up, Erikur Erikssons solution in previous post gives me a very fast query time( around 500ms ) when running against a cte with XML.
When i try to run it on a single record from the table that actually contains the XML column, the query takes about 4 sec. Can someone tell me why that is? Query is below and exec plan is attached.
Thanks guys! P.S. The first cross apply is to avoid a key lookup to get the XML, as that column is ntext and is not indexed.
select cdxml.Misc_ID
, [CD Flag] = BCD.DATA.value('(BCD05/@_)[1]','char(1)')
, [Doc Line] = _CDD.DATA.value('@docLine', 'int')
, [Cus Item Number] = LIN3.DATA.value('@_','char(30)')
, [Line Amt] = CDD4.DATA.value('@_','varchar(15)')
, [Reason Cd] = CDD1.DATA.value('@_','char(3)')
from XMLDoc edxd
cross apply ( select misc_id, cast(XMLText as XML)
FROM XMLDoc x
where x.VPID = edxd.VPID
) cdxml(misc_id, DATA)
cross apply
cdxml.DATA.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
cross apply (
cdxml.DATA.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') _CDD(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY _CDD.DATA.nodes('LIN/LIN03') LIN3(DATA)
)
where edxd.Misc_ID = 249331
July 6, 2018 at 10:25 am
Jackie Lowery - Friday, July 6, 2018 7:42 AMSorry for the late response on this. Project got put on the backburner for a couple weeks. So, to catch up, Erikur Erikssons solution in previous post gives me a very fast query time( around 500ms ) when running against a cte with XML.
When i try to run it on a single record from the table that actually contains the XML column, the query takes about 4 sec. Can someone tell me why that is? Query is below and exec plan is attached.Thanks guys! P.S. The first cross apply is to avoid a key lookup to get the XML, as that column is ntext and is not indexed.
select cdxml.Misc_ID
, [CD Flag] = BCD.DATA.value('(BCD05/@_)[1]','char(1)')
, [Doc Line] = _CDD.DATA.value('@docLine', 'int')
, [Cus Item Number] = LIN3.DATA.value('@_','char(30)')
, [Line Amt] = CDD4.DATA.value('@_','varchar(15)')
, [Reason Cd] = CDD1.DATA.value('@_','char(3)')
from XMLDoc edxd
cross apply ( select misc_id, cast(XMLText as XML)
FROM XMLDoc x
where x.VPID = edxd.VPID
) cdxml(misc_id, DATA)
cross apply
cdxml.DATA.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
cross apply (
cdxml.DATA.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') _CDD(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY _CDD.DATA.nodes('LIN/LIN03') LIN3(DATA)
)
where edxd.Misc_ID = 249331
My guess is that the server has the default "cost threshold for parallelism" settings, try running the query with "OPTION (MAXDOP 1)"
😎
July 6, 2018 at 1:40 pm
Eirikur Eiriksson - Friday, July 6, 2018 10:25 AMMy guess is that the server has the default "cost threshold for parallelism" settings, try running the query with "OPTION (MAXDOP 1)"
😎
query takes 4 sec with or without that option
July 7, 2018 at 3:23 am
Jackie Lowery - Friday, July 6, 2018 1:40 PMEirikur Eiriksson - Friday, July 6, 2018 10:25 AMMy guess is that the server has the default "cost threshold for parallelism" settings, try running the query with "OPTION (MAXDOP 1)"
😎query takes 4 sec with or without that option
I would try to dump the sub-query into a temporary table with a conversion to XML for the DATA column and then execute the XQuery against it.
😎
Ping back if you need any further help on this.
July 7, 2018 at 8:57 am
Eirikur Eiriksson - Saturday, July 7, 2018 3:23 AMI would try to dump the sub-query into a temporary table with a conversion to XML for the DATA column and then execute the XQuery against it.
😎Ping back if you need any further help on this.
How would i go about assigning the XML to an @xml variable in the query? This code takes 168ms to return the 119 rows from the xml. Crazy right!?
DECLARE @xml XML
select
@xml = x.XMLText
FROM XMLDoc x
where x.Misc_ID = 249331
select edxd.Misc_ID
, [CD Flag] = BCD.DATA.value('(BCD05/@_)[1]','char(1)')
, [Doc Line] = _CDD.DATA.value('@docLine', 'int')
, [Cus Item Number] = LIN3.DATA.value('@_','char(30)')
, [Line Amt] = CDD4.DATA.value('@_','varchar(15)')
, [Reason Cd] = CDD1.DATA.value('@_','char(3)')
from XMLDoc edxd
cross apply
@xml.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
cross apply (
@xml.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') _CDD(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY _CDD.DATA.nodes('LIN/LIN03') LIN3(DATA)
)
where edxd.Misc_ID = 249331
July 7, 2018 at 12:09 pm
Eirikur Eiriksson - Saturday, July 7, 2018 3:23 AMI would try to dump the sub-query into a temporary table with a conversion to XML for the DATA column and then execute the XQuery against it.
😎Ping back if you need any further help on this.
That was the answer i needed. I put the XML into a table variable and the query that returned 119 rows now only takes 125ms. I can pull a year of records for a customer in 5 sec.
Thanks so much man. Great work!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply