XML nodes help

  • Jackie Lowery - Saturday, July 7, 2018 12:09 PM

    Eirikur Eiriksson - Saturday, July 7, 2018 3:23 AM

    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.

    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!

    You are very welcome.
    😎

    Just a thought, if the initial XML result set is large, you will be better off using a temporary table rather than a table variable.

    Another though is that you could add a persisted calculated column, here is an example

    USE TEEST;
    GO
    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.TBL_TEST_XML_CONVERT') IS NOT NULL DROP TABLE dbo.TBL_TEST_XML_CONVERT;

    CREATE TABLE dbo.TBL_TEST_XML_CONVERT
    (
      TXC_ID  INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_XML_CONVERT_TXC_ID PRIMARY KEY CLUSTERED
     ,TXC_NTEXT NTEXT     NOT NULL
     ,TXC_XML  AS (CONVERT(XML,TXC_NTEXT,0)) PERSISTED
    );
    DECLARE @TXML NVARCHAR(MAX) =
    N'<RESULTS>
    <ROW>
    <COLUMN NAME="FIRSTNAME"><![CDATA[John]]></COLUMN>
    <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
    <COLUMN NAME="COUNTRY"><![CDATA[DK]]></COLUMN>
    </ROW>
    <ROW>
    <COLUMN NAME="FIRSTNAME"><![CDATA[Jane]]></COLUMN>
    <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
    <COLUMN NAME="COUNTRY"><![CDATA[SK]]></COLUMN>
    </ROW>
    <ROW>
    <COLUMN NAME="FIRSTNAME"><![CDATA[Joan]]></COLUMN>
    <COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
    <COLUMN NAME="COUNTRY"><![CDATA[UK]]></COLUMN>
    </ROW>
    </RESULTS>';
    INSERT INTO dbo.TBL_TEST_XML_CONVERT(TXC_NTEXT)
    VALUES
    (CONVERT(NTEXT,@TXML,0))
    ,(CONVERT(NTEXT,@TXML,0))
    ,(CONVERT(NTEXT,@TXML,0))
    ,(CONVERT(NTEXT,@TXML,0))
    ,(CONVERT(NTEXT,@TXML,0))
    ,(CONVERT(NTEXT,@TXML,0))
    ;

    SELECT
      TXC.TXC_ID
     ,TXC.TXC_NTEXT
     ,TXC.TXC_XML
    FROM  dbo.TBL_TEST_XML_CONVERT  TXC;

  • Is it possible to use a persisted calculated column when there are errors in some of the XML?
    I can't use try_convert b/c the db compat level is server 2005.  It's an old app, so not sure i can change that.
  • Jackie Lowery - Sunday, July 8, 2018 4:23 PM

    Is it possible to use a persisted calculated column when there are errors in some of the XML?
    I can't use try_convert b/c the db compat level is server 2005.  It's an old app, so not sure i can change that.

    What is the actual version you are running on?
    😎

    You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.

  • Eirikur Eiriksson - Monday, July 9, 2018 4:59 AM

    What is the actual version you are running on?
    😎

    You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.

    11.0.7001.0

  • Jackie Lowery - Monday, July 9, 2018 5:04 AM

    Eirikur Eiriksson - Monday, July 9, 2018 4:59 AM

    What is the actual version you are running on?
    😎

    You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.

    11.0.7001.0

    That is 2012 SP4, you should have no problems implementing the calculate column on this version.
    😎

  • I get this error:

    Msg 195, Level 15, State 10, Line 1
    'try_convert' is not a recognized built-in function name.

  • I changed the db compat level on our test server db to SQL 2012.  The try_convert function works on it now.  What things should i be concerned with when changing that in production?

  • I made the change to db compat level and created the XML computed column.  Now the query takes 12 sec. to return 119 rows for a single CDA.  I'm at a loss.  Here is code and exec plan.  Why would computed column be so much slower than the table variable?  I need the computed column to work b/c of the WMS system i have to use the query in.


    ;with ctCdaDocTypes /* Get Doc Types (i.e. 812) for CDA to filter Misc table by DocType */
    as
    (
        select Doc_ID
        from EDIStdDocs
        where DGID = 'CDA'
        group by Doc_ID
    )
    , ctTP_PartIDs /* Get Partner IDs to filter the Misc table by TP_PartID */
    as
    (    
        SELECT TP_PartID, ShipTo_CustID Cus_No FROM edi_01..ShipTo UNION SELECT TP_PartID, TP_ID Cus_No FROM edi_01..Trade
    )

    select x.Misc_ID
        , DateRecv
        , [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(30)')
        , [Reason Cd] = CDD1.DATA.value('@_','char(3)')
    from (
        select misc_id, DateRecv ,DocType, TP_PartID
        from Misc
        where Seg_ID = 1
        AND DocType IN (select Doc_ID from ctCdaDocTypes) /* CDA doc type is usually 812 */
    ) m
    join ctTP_PartIDs t on m.TP_PartID = t.TP_PartID
    join XMLDoc x on x.Misc_ID = m.Misc_ID
    cross apply
      x.docXML.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
    cross apply (
      x.docXML.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 m.DateRecv >= '01/01/2018' AND m.DateRecv <= GETDATE()
        AND m.Misc_ID = 249331 AND t.Cus_No IN ('WALM01')

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply