none
How do I convert these inner child XML fields to be SQL Server data rows? RRS feed

  • Question

  • How do I convert these inner child XML fields to be SQL Server data rows?

    <CustomRecord Source="A NAME " Ticker="A Name">
      <field ID="ION_COMMENT2(-18149)">order basis only</field>
      <field ID="ION_O_ASKQTY(-18116)">1.0</field>
      <field ID="ION_O_BIDQTY(-18114)">1.0</field>
      <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
      <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
      <field ID="ION_ASKSPREAD2(-18005)">0</field>
      <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
      <field ID="BID(22)">0</field>
      <field ID="ASK(25)">0</field>
      <field ID="YIELD(35)">0</field>
      <field ID="MID_PRICE(134)">0</field>
      <field ID="MKT_MKR_NM(214)"></field>
      <field ID="BID_YIELD(362)">0</field>
      <field ID="ASK_YIELD(363)">0</field>
      <field ID="COMMENTTXT(553)">fl</field>
      <field ID="BUYMARGIN(777)">0</field>
      <field ID="SELLMARGIN(779)">0</field>
      <field ID="GEN_VAL1(996)">0</field>
      <field ID="GEN_VAL2(997)">0</field>
      <field ID="GEN_VAL3(998)">0</field>
      <field ID="GEN_VAL4(999)">0</field>
      <field ID="ASK_SPREAD(3296)">101</field>
      <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>


    Tuesday, April 9, 2019 3:08 PM

Answers

  • Hi TilleyTech Ltd,

    I adjusted the solution based on your latest input.

    This way you will get NULL value for the field XML elements that do not appear. But the resultset structure will be the same. This way it is easier to integrate it with what you have around it, i.e. stored procedure, etc.

    Additional benefit is that the field XML element sequential position is irrelevant, just its ID attribute value makes sure it is a correct field element.

    SQL:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_O_ASKQTY(-18116)">1.0</field>
    	<field ID="ION_O_BIDQTY(-18114)">1.0</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    	<field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
    	<field ID="BID(22)">0</field>
    	<field ID="ASK(25)">0</field>
    	<field ID="YIELD(35)">0</field>
    	<field ID="MID_PRICE(134)">0</field>
    	<field ID="BID_YIELD(362)">0</field>
    	<field ID="ASK_YIELD(363)">0</field>
    	<field ID="COMMENTTXT(553)">fl</field>
    	<field ID="BUYMARGIN(777)">0</field>
    	<field ID="SELLMARGIN(779)">0</field>
    	<field ID="GEN_VAL1(996)">0</field>
    	<field ID="GEN_VAL2(997)">0</field>
    	<field ID="GEN_VAL3(998)">0</field>
    	<field ID="GEN_VAL4(999)">0</field>
    	<field ID="ASK_SPREAD(3296)">101</field>
    	<field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    </CustomRecord>');
    
    ;WITH rs AS
    (
    	SELECT ISIN
    		, col.value('(field[@ID = "ION_COMMENT2(-18149)"])[1]','VARCHAR(20)') AS ION_COMMENT2
    		, col.value('(field[@ID="ION_O_ASKQTY(-18116)"])[1]','DECIMAL(10,2)') AS ION_O_ASKQTY
    		, col.value('(field[@ID = "ION_DESC(-18099)"])[1]','VARCHAR(20)') AS ION_DESC
    		-- put here the rest of the fields
    		, col.value('(field[@ID="BID_SPREAD(3303)"])[1]','DECIMAL(10,2)') AS BID_SPREAD
    	FROM @ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes('/CustomRecord') AS tab(col)
    )
    SELECT * FROM rs;
    Wednesday, April 10, 2019 1:55 PM
  • Hey

    Thanks so much for your prompt responses. This last one seems to be the winner; thanks so much.

    I can combine the col.value(field query) with a query against the fixed table that contains the fields we are requesting.

    1. Query fields we will request.

    2. Build this bit

       SELECT ISIN,

    <build it from (1) >

    FROM ArtxfeedXmlInfo tbl
    CROSS APPLY tbl.[data].nodes('/ARTXPriceFeedRecord') AS tab(col)

    3. Exec the string of the query be built.

    Thanks alot for your swift assistance.

    Leigh

    • Marked as answer by TilleyTech Ltd Wednesday, April 10, 2019 3:09 PM
    Wednesday, April 10, 2019 2:51 PM
  • For anyone interested, and for anyone else to use, I did this in the end

    1) Get the fields I'm interested in

    2) Build the query (cursor loop as inexpensive at 26 rows, but feel free to suggest another way; for me cursors are not bad if used in certain situations but many feel that they are!) :)

    3) EXEC the string.


    DECLARE @fieldsCursor CURSOR
    DECLARE @field VARCHAR(MAX)
    DECLARE @query VARCHAR(MAX)

    BEGIN
        SET @fieldsCursor = CURSOR FOR
        SELECT
      '(field[@ID = ' + '"' +[name] + '(' + CONVERT(VARCHAR(10),[ID]) + ')"])[1]'', ''VARCHAR(MAX)'') AS ' + [Name]
    Name
    FROM ArtxFieldsToDecode
    ORDER BY [Name]

    set @query = ' ISIN,'
        OPEN @fieldsCursor 
        FETCH NEXT FROM @fieldsCursor 
        INTO @field

        WHILE @@FETCH_STATUS = 0
        BEGIN
          
      set @query = @query + 'col.value(''' + @field + ','

          FETCH NEXT FROM @fieldsCursor 
          INTO @field 
        END; 

        CLOSE @fieldsCursor
        DEALLOCATE @fieldsCursor
    END

    DECLARE @finalQuery VARCHAR(MAX)

    SET @finalQuery = 'SELECT' + SUBSTRING(@query, 1, LEN(@query)-1) +
    ' FROM ArtxfeedXmlInfo tbl' +
    ' CROSS APPLY tbl.[data].nodes(''/ARTXPriceFeedRecord'') AS tab(col)'

    --PRINT(@finalQuery)
    EXEC(@finalQuery)

    Thursday, April 11, 2019 9:45 AM
  • Hi TilleyTech Ltd,

    Here is how to do it without a CURSOR:

    DECLARE @ArtxFieldsToDecode TABLE ([name] VARCHAR(50), dataType VARCHAR(20), alias VARCHAR(20));
    INSERT INTO @ArtxFieldsToDecode
    VALUES ('ION_COMMENT2(-18149)', 'VARCHAR(20)', 'COMMENT2')
    	, ('ION_O_ASKQTY(-18116)', 'DECIMAL(10,2)', 'ASKQTY')
    	, ('BID_SPREAD(3303)', 'DECIMAL(10,2)', 'BID_SPREAD');
    
    DECLARE @finalQuery VARCHAR(MAX) = 'SELECT ISIN'
    	, @name VARCHAR(50), @dataType VARCHAR(20), @alias VARCHAR(20)
    	, @RowCount INT = (SELECT COUNT(*) FROM @ArtxFieldsToDecode);
    
    -- loop through the table without a cursor row by row
    WHILE @RowCount > 0 BEGIN
       SELECT @name = [name], @dataType = dataType, @alias = alias
       FROM @ArtxFieldsToDecode 
       ORDER BY [name] DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
    
       -- cool recursive update of the @finalQuery variable
       SET @finalQuery += CHAR(13)
    		+ ', col.value(''(field[@ID="' + @name+ '"])[1]'','
    		+ '''' + @dataType + ''') AS ' + @alias;
    
       SET @RowCount -= 1;
    END
    
    SET @finalQuery += CHAR(13) + ' FROM ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes(''/CustomRecord'') AS tab(col);'
    
    PRINT @finalQuery;


    Or, even better, without any loop, just using set based statement:

    DECLARE @ArtxFieldsToDecode TABLE ([name] VARCHAR(50), dataType VARCHAR(20), alias VARCHAR(20));
    INSERT INTO @ArtxFieldsToDecode
    VALUES ('ION_COMMENT2(-18149)', 'VARCHAR(20)', 'COMMENT2')
    	, ('ION_O_ASKQTY(-18116)', 'DECIMAL(10,2)', 'ASKQTY')
    	, ('BID_SPREAD(3303)', 'DECIMAL(10,2)', 'BID_SPREAD');
    
    DECLARE @finalQuery VARCHAR(MAX) = 'SELECT ISIN';
    
    -- set based recursive update of the @finalQuery variable
    SELECT @finalQuery = @finalQuery + CHAR(13)
    	+ ', col.value(''(field[@ID="' + [name] + '"])[1]'','
    	+ '''' + dataType + ''') AS ' + alias
    FROM @ArtxFieldsToDecode;
    
    SET @finalQuery += CHAR(13) + 'FROM ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes(''/CustomRecord'') AS tab(col);'
    
    PRINT @finalQuery;


    Thursday, April 11, 2019 3:49 PM
  • Hi TilleyTech Ltd,

    In such case, there is a small adjustment in order:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
    </CustomRecord>');
    
    --INSERT INTO <anotherTable>, uncomment it when you are ready
    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(50)') AS ID
          , col.value('(field)[1]','VARCHAR(50)') AS columnName
    FROM (SELECT ISIN, [data].query('<root>
    			{
    			  for $field in /CustomRecord/field
    			  return <row>
    					<id>{data($field/@ID)}</id>
    					<field>{data($field)}</field>
    				</row>
    			}</root>') AS dataXML
    		FROM @ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col);
    Monday, April 15, 2019 3:08 PM
  • Hi

    Thanks the PARSENAME approach shaved 3 seconds off the query time versus the original SUBSTRING +  CHARINDEX 

    SELECT ISIN

          --, SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
      , PARSENAME(REPLACE(col.value('(id)[1]', 'VARCHAR(50)'), '(','.'),2) AS pureID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value],
      GetDate()
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper

    • Marked as answer by TilleyTech Ltd Wednesday, April 17, 2019 10:49 AM
    Tuesday, April 16, 2019 3:04 PM
  • Hi TilleyTech Ltd,

    Here is how to expose the @Source attribute:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="MKT_MKR_NM(214)">UT1NGJ_MT</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="LookingForMe" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">77</field>
    </CustomRecord>');
    
    -- just to see it, XQuery FLWOR expression to re-shape the XML
    SELECT ISIN, [data].query('<root>
        {
          for $field in /CustomRecord/field
          return <row>
    				<id>{data($field/@ID)}</id>
    				<field>{data($field)}</field>
    				<source>{data(CustomRecord/@Source)}</source>
    			</row>
    }</root>') AS dataXML
    FROM @ArtxfeedXmlInfo;

    Wednesday, May 8, 2019 3:15 PM

All replies

  • Hi TilleyTech Ltd,

    SQL:

    DECLARE @xml XML = '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_O_ASKQTY(-18116)">1.0</field>
    	<field ID="ION_O_BIDQTY(-18114)">1.0</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    	<field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
    	<field ID="BID(22)">0</field>
    	<field ID="ASK(25)">0</field>
    	<field ID="YIELD(35)">0</field>
    	<field ID="MID_PRICE(134)">0</field>
    	<field ID="BID_YIELD(362)">0</field>
    	<field ID="ASK_YIELD(363)">0</field>
    	<field ID="COMMENTTXT(553)">fl</field>
    	<field ID="BUYMARGIN(777)">0</field>
    	<field ID="SELLMARGIN(779)">0</field>
    	<field ID="GEN_VAL1(996)">0</field>
    	<field ID="GEN_VAL2(997)">0</field>
    	<field ID="GEN_VAL3(998)">0</field>
    	<field ID="GEN_VAL4(999)">0</field>
    	<field ID="ASK_SPREAD(3296)">101</field>
    	<field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>';
    
    ;WITH rs AS
    (
       SELECT col.value('(field)[1]','VARCHAR(20)') AS ION_COMMENT2
          , col.value('(field)[2]','DECIMAL(10,2)') AS ION_O_ASKQTY
    	  -- put here the rest of the fields
          , col.value('(field)[23]','DECIMAL(10,2)') AS BID_SPREAD
       FROM @xml.nodes('/CustomRecord') AS tab(col)
    )
    SELECT * FROM rs;

    Output:

    ION_COMMENT2	ION_O_ASKQTY	BID_SPREAD
    order basis only	1.00	                    100.25
    
    Tuesday, April 9, 2019 4:44 PM
  • Hi

    That's great; thanks for the fast response.

    For my setup, it's actually a realtime feed performing a multi-threaded bulkcopy from a C# app.

    The table is called ArtxfeedXmlInfo and the XML column is called. imaginatively, data. :)

    There won't always be all columns (currently max 26) but the feed I'm connected to from my C# app sends only fields that update. So sometimes we'll have many, sometimes only 1-2.

    Is there a way to do a generic 1..n fields on this please?

    Thanks in advance for your assistance.

    Leigh Tilley

    Wednesday, April 10, 2019 9:34 AM
  • Hey

    This seems workable and I guess I'll get null for the items that do not appear.

    SELECT ISIN,
    col.value('(field)[1]','VARCHAR(20)') AS ION_COMMENT2
          , col.value('(field)[2]','DECIMAL(10,2)') AS ION_O_ASKQTY
      -- put here the rest of the fields
          , col.value('(field)[23]','DECIMAL(10,2)') AS BID_SPREAD
       FROM ArtxfeedXmlInfo artx 
       CROSS APPLY artx.[data].nodes('/ARTXPriceFeedRecord') AS tab(col)

    I am still interested in if there is a 1..n approach, as i'll wrap this in a stored proc/job so 1..n would be nice in case I expand it. For example, the fields we know we'll ask for actually live in a table too (stored proc returns these to pass in as an int[] to the API such that it severely cuts down the realtime messaging specifying what we are interested in! :)

    Wednesday, April 10, 2019 12:58 PM
  • Hey

    This seems workable and I guess I'll get null for the items that do not appear.

    SELECT ISIN,
    col.value('(field)[1]','VARCHAR(20)') AS ION_COMMENT2
          , col.value('(field)[2]','DECIMAL(10,2)') AS ION_O_ASKQTY
      -- put here the rest of the fields
          , col.value('(field)[23]','DECIMAL(10,2)') AS BID_SPREAD
       FROM ArtxfeedXmlInfo artx 
       CROSS APPLY artx.[data].nodes('/ARTXPriceFeedRecord') AS tab(col)

    I am still interested in if there is a 1..n approach, as i'll wrap this in a stored proc/job so 1..n would be nice in case I expand it. For example, the fields we know we'll ask for actually live in a table too (stored proc returns these to pass in as an int[] to the API such that it severely cuts down the realtime messaging specifying what we are interested in! :)

    you can use a method like this to get only available fields dynamically

    DECLARE @x XML = '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_O_ASKQTY(-18116)">1.0</field>
    	<field ID="ION_O_BIDQTY(-18114)">1.0</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    	<field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
    	<field ID="BID(22)">0</field>
    	<field ID="ASK(25)">0</field>
    	<field ID="YIELD(35)">0</field>
    	<field ID="MID_PRICE(134)">0</field>
    	<field ID="MKT_MKR_NM(214)">UT1NGJ_MT</field>
    	<field ID="BID_YIELD(362)">0</field>
    	<field ID="ASK_YIELD(363)">0</field>
    	<field ID="COMMENTTXT(553)">fl</field>
    	<field ID="BUYMARGIN(777)">0</field>
    	<field ID="SELLMARGIN(779)">0</field>
    	<field ID="GEN_VAL1(996)">0</field>
    	<field ID="GEN_VAL2(997)">0</field>
    	<field ID="GEN_VAL3(998)">0</field>
    	<field ID="GEN_VAL4(999)">0</field>
    	<field ID="ASK_SPREAD(3296)">101</field>
    	<field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>'
    
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
    
    
    SELECT u.value('@ID','varchar(100)') AS FieldName,
    u.value('.','varchar(1000)') AS FieldValue
    INTO #Temp
    FROM @x.nodes('/CustomRecord/field')t(u)
    
    DECLARE @ColumnList varchar(max)
    
    SET @ColumnList = STUFF((SELECT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')
    
    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'
    
    EXEC (@SQL)
    
    /*
    Ouput
    -----------------------------------------------------
    ION_COMMENT2(-18149)	ION_O_ASKQTY(-18116)	ION_O_BIDQTY(-18114)	ION_DESC(-18099)	ION_BIDASSETSWAPSPREAD(-18009)	ION_ASKSPREAD2(-18005)	ION_ASKASSETSWAPSPREAD(-18002)	BID(22)	ASK(25)	YIELD(35)	MID_PRICE(134)	MKT_MKR_NM(214)	BID_YIELD(362)	ASK_YIELD(363)	COMMENTTXT(553)	BUYMARGIN(777)	SELLMARGIN(779)	GEN_VAL1(996)	GEN_VAL2(997)	GEN_VAL3(998)	GEN_VAL4(999)	ASK_SPREAD(3296)	BID_SPREAD(3303)
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    order basis only	1.0	1.0	LAMON 7.625 PERPETUAL -33	0	0	0	0	0	0	0	UT1NGJ_MT	0	0	fl	0	0	0	0	0	0	101	100.25
    
    */
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 10, 2019 1:05 PM
  • Or if its a table column then

    DECLARE @t table
    (
    x XML 
    )
    INSERT @T
    values
    ( '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_O_ASKQTY(-18116)">1.0</field>
    	<field ID="ION_O_BIDQTY(-18114)">1.0</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    	<field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
    	<field ID="BID(22)">0</field>
    	<field ID="ASK(25)">0</field>
    	<field ID="YIELD(35)">0</field>
    	<field ID="MID_PRICE(134)">0</field>
    	<field ID="MKT_MKR_NM(214)">UT1NGJ_MT</field>
    	<field ID="BID_YIELD(362)">0</field>
    	<field ID="ASK_YIELD(363)">0</field>
    	<field ID="COMMENTTXT(553)">fl</field>
    	<field ID="BUYMARGIN(777)">0</field>
    	<field ID="SELLMARGIN(779)">0</field>
    	<field ID="GEN_VAL1(996)">0</field>
    	<field ID="GEN_VAL2(997)">0</field>
    	<field ID="GEN_VAL3(998)">0</field>
    	<field ID="GEN_VAL4(999)">0</field>
    	<field ID="ASK_SPREAD(3296)">101</field>
    	<field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
    
    
    SELECT u.value('@ID','varchar(100)') AS FieldName,
    u.value('.','varchar(1000)') AS FieldValue
    INTO #Temp
    FROM @t t
    CROSS APPLY x.nodes('/CustomRecord/field')m(u)
    
    DECLARE @ColumnList varchar(max)
    
    SET @ColumnList = STUFF((SELECT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')
    
    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'
    
    EXEC (@SQL)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 10, 2019 1:07 PM
  • Hey

    Thanks alot for the prompt response.

    I am trying this at present against the actual table itself with the varying XML data column:


    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

    SELECT ISIN, u.value('@ID','varchar(MAX)') AS FieldName,
    u.value('.','varchar(MAX)') AS FieldValue
    INTO #Temp
    FROM ArtxfeedXmlInfo artx 
       CROSS APPLY artx.[data].nodes('/ARTXPriceFeedRecord/field') AS t(u)

    DECLARE @ColumnList varchar(max)

    SET @ColumnList = STUFF((SELECT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')

    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'

    EXEC (@SQL)

    It gives me this error:

    Msg 8156, Level 16, State 1, Line 12
    The column 'GEN_VAL4(999)' was specified multiple times for 'p'.

    with the field name changing each time I try it.

    What's wrong with this please? :)

    Thanks so much for responses and help sp far!

    Leigh


    Wednesday, April 10, 2019 1:28 PM
  • Ah perils of fast scrolling - didn't see your second reply.

    So I tried this too

    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

    SELECT u.value('@ID','varchar(100)') AS FieldName,
    u.value('.','varchar(1000)') AS FieldValue
    INTO #Temp
    FROM ArtxfeedXmlInfo artx
    CROSS APPLY [data].nodes('/ARTXPriceFeedRecord/field')m(u)

    DECLARE @ColumnList varchar(max)

    SET @ColumnList = STUFF((SELECT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')

    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'

    EXEC (@SQL)

    but it gives the same error:

    Msg 8156, Level 16, State 1, Line 12
    The column 'ION_DESC(-18099)' was specified multiple times for 'p'. 

    Wednesday, April 10, 2019 1:42 PM
  • Ah perils of fast scrolling - didn't see your second reply.

    So I tried this too

    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

    SELECT u.value('@ID','varchar(100)') AS FieldName,
    u.value('.','varchar(1000)') AS FieldValue
    INTO #Temp
    FROM ArtxfeedXmlInfo artx
    CROSS APPLY [data].nodes('/ARTXPriceFeedRecord/field')m(u)

    DECLARE @ColumnList varchar(max)

    SET @ColumnList = STUFF((SELECT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')

    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'

    EXEC (@SQL)

    but it gives the same error:

    Msg 8156, Level 16, State 1, Line 12
    The column 'ION_DESC(-18099)' was specified multiple times for 'p'. 

    This means your XML had multiple values specified for the same column

    try like this and see if its any better

    F OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
    
    SELECT u.value('@ID','varchar(100)') AS FieldName,
    u.value('.','varchar(1000)') AS FieldValue
    INTO #Temp
    FROM ArtxfeedXmlInfo artx
    CROSS APPLY [data].nodes('/ARTXPriceFeedRecord/field')m(u)
    
    DECLARE @ColumnList varchar(max)
    
    SET @ColumnList = STUFF((SELECT DISTINCT ',[' + FieldName + ']'
    FROM #Temp
    FOR XML PATH('')),1,1,'')
    
    DECLARE @SQL varchar(max) = 'SELECT *
    FROM #Temp t
    PIVOT(MAX(FieldValue) FOR FieldName IN (' + @ColumnList + '))p'
    
    EXEC (@SQL)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 10, 2019 1:44 PM
  • Hi TilleyTech Ltd,

    I adjusted the solution based on your latest input.

    This way you will get NULL value for the field XML elements that do not appear. But the resultset structure will be the same. This way it is easier to integrate it with what you have around it, i.e. stored procedure, etc.

    Additional benefit is that the field XML element sequential position is irrelevant, just its ID attribute value makes sure it is a correct field element.

    SQL:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_O_ASKQTY(-18116)">1.0</field>
    	<field ID="ION_O_BIDQTY(-18114)">1.0</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    	<field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
    	<field ID="BID(22)">0</field>
    	<field ID="ASK(25)">0</field>
    	<field ID="YIELD(35)">0</field>
    	<field ID="MID_PRICE(134)">0</field>
    	<field ID="BID_YIELD(362)">0</field>
    	<field ID="ASK_YIELD(363)">0</field>
    	<field ID="COMMENTTXT(553)">fl</field>
    	<field ID="BUYMARGIN(777)">0</field>
    	<field ID="SELLMARGIN(779)">0</field>
    	<field ID="GEN_VAL1(996)">0</field>
    	<field ID="GEN_VAL2(997)">0</field>
    	<field ID="GEN_VAL3(998)">0</field>
    	<field ID="GEN_VAL4(999)">0</field>
    	<field ID="ASK_SPREAD(3296)">101</field>
    	<field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="A NAME " Ticker="A Name">
    	<field ID="ION_COMMENT2(-18149)">order basis only</field>
    	<field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
    	<field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
    	<field ID="ION_ASKSPREAD2(-18005)">0</field>
    </CustomRecord>');
    
    ;WITH rs AS
    (
    	SELECT ISIN
    		, col.value('(field[@ID = "ION_COMMENT2(-18149)"])[1]','VARCHAR(20)') AS ION_COMMENT2
    		, col.value('(field[@ID="ION_O_ASKQTY(-18116)"])[1]','DECIMAL(10,2)') AS ION_O_ASKQTY
    		, col.value('(field[@ID = "ION_DESC(-18099)"])[1]','VARCHAR(20)') AS ION_DESC
    		-- put here the rest of the fields
    		, col.value('(field[@ID="BID_SPREAD(3303)"])[1]','DECIMAL(10,2)') AS BID_SPREAD
    	FROM @ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes('/CustomRecord') AS tab(col)
    )
    SELECT * FROM rs;
    Wednesday, April 10, 2019 1:55 PM
  • Hey

    Thanks so much for your prompt responses. This last one seems to be the winner; thanks so much.

    I can combine the col.value(field query) with a query against the fixed table that contains the fields we are requesting.

    1. Query fields we will request.

    2. Build this bit

       SELECT ISIN,

    <build it from (1) >

    FROM ArtxfeedXmlInfo tbl
    CROSS APPLY tbl.[data].nodes('/ARTXPriceFeedRecord') AS tab(col)

    3. Exec the string of the query be built.

    Thanks alot for your swift assistance.

    Leigh

    • Marked as answer by TilleyTech Ltd Wednesday, April 10, 2019 3:09 PM
    Wednesday, April 10, 2019 2:51 PM
  • Hi TilleyTech Ltd,

    Glad to hear that the proposed solution is working for you.

    Please don't forget to click on the 'Mark as Answer' the responses that resolved your issue to help others to solve similar issues.

    Wednesday, April 10, 2019 3:00 PM
  • Hey

    Yeah I've written part 1 pretty much

    SELECT
      '(field[@ID = ' + '"' +[name] + '(' + CONVERT(VARCHAR(10),[ID]) + ')")[1]'', ''VARCHAR(MAX)'') AS ' + [Name]
    Name
    FROM ArtxFieldsToDecode
    Order by Name 

    which can be interrogated to build the fields list for the above. Glue it all and EXEC the string.

    Thanks alot everyone.

    Leigh

    • Marked as answer by TilleyTech Ltd Wednesday, April 10, 2019 3:06 PM
    • Unmarked as answer by TilleyTech Ltd Wednesday, April 10, 2019 3:08 PM
    Wednesday, April 10, 2019 3:06 PM
  • For anyone interested, and for anyone else to use, I did this in the end

    1) Get the fields I'm interested in

    2) Build the query (cursor loop as inexpensive at 26 rows, but feel free to suggest another way; for me cursors are not bad if used in certain situations but many feel that they are!) :)

    3) EXEC the string.


    DECLARE @fieldsCursor CURSOR
    DECLARE @field VARCHAR(MAX)
    DECLARE @query VARCHAR(MAX)

    BEGIN
        SET @fieldsCursor = CURSOR FOR
        SELECT
      '(field[@ID = ' + '"' +[name] + '(' + CONVERT(VARCHAR(10),[ID]) + ')"])[1]'', ''VARCHAR(MAX)'') AS ' + [Name]
    Name
    FROM ArtxFieldsToDecode
    ORDER BY [Name]

    set @query = ' ISIN,'
        OPEN @fieldsCursor 
        FETCH NEXT FROM @fieldsCursor 
        INTO @field

        WHILE @@FETCH_STATUS = 0
        BEGIN
          
      set @query = @query + 'col.value(''' + @field + ','

          FETCH NEXT FROM @fieldsCursor 
          INTO @field 
        END; 

        CLOSE @fieldsCursor
        DEALLOCATE @fieldsCursor
    END

    DECLARE @finalQuery VARCHAR(MAX)

    SET @finalQuery = 'SELECT' + SUBSTRING(@query, 1, LEN(@query)-1) +
    ' FROM ArtxfeedXmlInfo tbl' +
    ' CROSS APPLY tbl.[data].nodes(''/ARTXPriceFeedRecord'') AS tab(col)'

    --PRINT(@finalQuery)
    EXEC(@finalQuery)

    Thursday, April 11, 2019 9:45 AM
  • Hi TilleyTech Ltd,

    Here is how to do it without a CURSOR:

    DECLARE @ArtxFieldsToDecode TABLE ([name] VARCHAR(50), dataType VARCHAR(20), alias VARCHAR(20));
    INSERT INTO @ArtxFieldsToDecode
    VALUES ('ION_COMMENT2(-18149)', 'VARCHAR(20)', 'COMMENT2')
    	, ('ION_O_ASKQTY(-18116)', 'DECIMAL(10,2)', 'ASKQTY')
    	, ('BID_SPREAD(3303)', 'DECIMAL(10,2)', 'BID_SPREAD');
    
    DECLARE @finalQuery VARCHAR(MAX) = 'SELECT ISIN'
    	, @name VARCHAR(50), @dataType VARCHAR(20), @alias VARCHAR(20)
    	, @RowCount INT = (SELECT COUNT(*) FROM @ArtxFieldsToDecode);
    
    -- loop through the table without a cursor row by row
    WHILE @RowCount > 0 BEGIN
       SELECT @name = [name], @dataType = dataType, @alias = alias
       FROM @ArtxFieldsToDecode 
       ORDER BY [name] DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
    
       -- cool recursive update of the @finalQuery variable
       SET @finalQuery += CHAR(13)
    		+ ', col.value(''(field[@ID="' + @name+ '"])[1]'','
    		+ '''' + @dataType + ''') AS ' + @alias;
    
       SET @RowCount -= 1;
    END
    
    SET @finalQuery += CHAR(13) + ' FROM ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes(''/CustomRecord'') AS tab(col);'
    
    PRINT @finalQuery;


    Or, even better, without any loop, just using set based statement:

    DECLARE @ArtxFieldsToDecode TABLE ([name] VARCHAR(50), dataType VARCHAR(20), alias VARCHAR(20));
    INSERT INTO @ArtxFieldsToDecode
    VALUES ('ION_COMMENT2(-18149)', 'VARCHAR(20)', 'COMMENT2')
    	, ('ION_O_ASKQTY(-18116)', 'DECIMAL(10,2)', 'ASKQTY')
    	, ('BID_SPREAD(3303)', 'DECIMAL(10,2)', 'BID_SPREAD');
    
    DECLARE @finalQuery VARCHAR(MAX) = 'SELECT ISIN';
    
    -- set based recursive update of the @finalQuery variable
    SELECT @finalQuery = @finalQuery + CHAR(13)
    	+ ', col.value(''(field[@ID="' + [name] + '"])[1]'','
    	+ '''' + dataType + ''') AS ' + alias
    FROM @ArtxFieldsToDecode;
    
    SET @finalQuery += CHAR(13) + 'FROM ArtxfeedXmlInfo tbl
    		CROSS APPLY tbl.[data].nodes(''/CustomRecord'') AS tab(col);'
    
    PRINT @finalQuery;


    Thursday, April 11, 2019 3:49 PM
  • That's great Yitzhak; thanks for taking the time to follow up with not 1 but 2 options! :)
    Monday, April 15, 2019 9:14 AM
  • Hey

    One more question actually; we are, thanks to your assistance on this thread, now producing a row with ISIN + 1..n cols from my core fields table.

    What would be the best way to do it if instead, I needed to produce new rows to insert into another table but each column in a new row:

    ISIN col1

    ISIN col2

    ISIN col3

    ISIN col4

    ..col26

    Thanks in advance for your assistance

    Leigh

    Monday, April 15, 2019 1:38 PM
  • Hi TilleyTech Ltd,

    For that XQuery FLWOR expression is the best.

    Here it is:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
    </CustomRecord>');
    
    -- just to see it, XQuery FLWOR expression to re-shape the XML
    SELECT ISIN, [data].query('<root>
        {
          for $field in /CustomRecord/field
          return <row>{data($field)}</row>
    }</root>') AS dataXML
    FROM @ArtxfeedXmlInfo;
    
    -- INSERT each <field> XML element into a new row
    --INSERT INTO anotherTable, uncomment it when you are ready
    SELECT ISIN
          , col.value('(.)[1]','VARCHAR(50)') AS columnName
    FROM (SELECT ISIN, [data].query('<root>
    		{
    		  for $field in /CustomRecord/field
    		  return <row>{data($field)}</row>
    		}</root>') AS dataXML
    		FROM @ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col);
    Monday, April 15, 2019 2:28 PM
  • Amazing mate; such a quick response from you. Query is very fast too!

    What's wrong with my @ID syntax? :) 

    -- INSERT each <field> XML element into a new row
    --INSERT INTO anotherTable, uncomment it when you are ready
    SELECT ''
    , col.value('(@ID)[1]','VARCHAR(MAX)') AS [FID]
        , col.value('(.)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>{data($field)}</row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo (NOLOCK)) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE isin = '<removed>'


    Monday, April 15, 2019 2:57 PM
  • Hi TilleyTech Ltd,

    In such case, there is a small adjustment in order:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="A NAME " Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
    </CustomRecord>');
    
    --INSERT INTO <anotherTable>, uncomment it when you are ready
    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(50)') AS ID
          , col.value('(field)[1]','VARCHAR(50)') AS columnName
    FROM (SELECT ISIN, [data].query('<root>
    			{
    			  for $field in /CustomRecord/field
    			  return <row>
    					<id>{data($field/@ID)}</id>
    					<field>{data($field)}</field>
    				</row>
    			}</root>') AS dataXML
    		FROM @ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col);
    Monday, April 15, 2019 3:08 PM
  • Hey

    That's perfect; thanks so much for your help!

    V powerful query! :)

    Monday, April 15, 2019 3:52 PM
  • Thanks so much for your help as always! :)

    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{fn:substring(data($field/@ID),1, fn:index-of(data($field/@ID),'(') -1 }</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    Before I delve further to fix the pos on '(' I thought I'd ask you as you are way more experienced in the XML query stuff. I need to get the FID without the (ID) that is coming back from API. Just arg 3, the bit where I want to do pos('(') -1 is currently wrong.

    Your advice greatly appreciated; or another way to do it. I began with T-SQL Substring on the outer value but it seems nicer to do it in the XML.

    Tuesday, April 16, 2019 9:49 AM
  • Actually, I'm now here :)

    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{fn:substring(data($field/@ID),1,functx:substring-before-last(data($field/@ID),'(') -1))}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)


    What do I need to tweak please?


    Tuesday, April 16, 2019 10:13 AM
  • Oops, actually I noticed all I need is the new functx function before last!

    SELECT ISIN

          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{functx:substring-before-last(data($field/@ID),'(')}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    I'm tweaking it now but your advice would be appreciated


    Tuesday, April 16, 2019 10:17 AM
  • Hey

    So this works:

    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{fn:substring(data($field/@ID),1,4)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    But this does not (what I really want :) ):

    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{fn:substring-before(data($field/@ID),'(')}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    What am I missing please? :)

    Tuesday, April 16, 2019 12:34 PM
  • Actually I tried this 

    SELECT ISIN
          , col.value('(id)[1]','VARCHAR(MAX)') AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      let $bracket := "&#40;"
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{fn:substring-before(data($field/@ID),$bracket)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    Msg 2395, Level 16, State 1, Line 14

    XQuery [ArtxfeedXmlInfo.data.query()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:substring-before()'

    So it doesn't exist? :)

    What else can I do? Before I go back to using SQL on the outer. 

    Thanks

    Leigh

    Tuesday, April 16, 2019 12:52 PM
  • I settled on

    SELECT ISIN
          , SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value]
    FROM (SELECT ISIN, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)

    But do let me know if it's possible in XQuery as I'm genuinely interested! :)

    Thanks

    Leigh

    Tuesday, April 16, 2019 1:05 PM
  • Hi TilleyTech Ltd,

    What version of SQL Server you are using?

    Tuesday, April 16, 2019 1:12 PM
  • Hi TilleyTech Ltd,

    Please try the following:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML); INSERT INTO @ArtxfeedXmlInfo VALUES (1, '<CustomRecord Source="A NAME " Ticker="A Name"> <field ID="ION_COMMENT2(-18149)">order basis only</field> <field ID="ION_O_ASKQTY(-18116)">1.0</field> <field ID="ION_O_BIDQTY(-18114)">1.0</field> <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field> <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field> <field ID="ION_ASKSPREAD2(-18005)">0</field> <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field> <field ID="BID(22)">0</field> <field ID="ASK(25)">0</field> <field ID="YIELD(35)">0</field> <field ID="MID_PRICE(134)">0</field> <field ID="BID_YIELD(362)">0</field> <field ID="ASK_YIELD(363)">0</field> <field ID="COMMENTTXT(553)">fl</field> <field ID="BUYMARGIN(777)">0</field> <field ID="SELLMARGIN(779)">0</field> <field ID="GEN_VAL1(996)">0</field> <field ID="GEN_VAL2(997)">0</field> <field ID="GEN_VAL3(998)">0</field> <field ID="GEN_VAL4(999)">0</field> <field ID="ASK_SPREAD(3296)">101</field> <field ID="BID_SPREAD(3303)">100.25</field> </CustomRecord>') , (2, '<CustomRecord Source="A NAME " Ticker="A Name"> <field ID="ION_COMMENT2(-18149)">order basis only</field> <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field> <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field> <field ID="ION_ASKSPREAD2(-18005)">0</field> </CustomRecord>'); --INSERT INTO <anotherTable>, uncomment it when you are ready SELECT ISIN , col.value('(id)[1]','VARCHAR(50)') AS ID , PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) as pristineID -- the best approach

    --, CAST('<x>' + REPLACE(col.value('(id)[1]','NVARCHAR(MAX)'),'(','</x><x>') + '</x>' AS XML).value('(x)[1]','NVARCHAR(MAX)') as pureID , col.value('(field)[1]','VARCHAR(50)') AS columnName FROM (SELECT ISIN, [data].query('<root> { for $field in /CustomRecord/field return <row> <id>{data($field/@ID)}</id> <field>{data($field)}</field> </row> }</root>') AS dataXML FROM @ArtxfeedXmlInfo) tbl CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col);

    Tuesday, April 16, 2019 2:00 PM
  • Version is (via @@version):

    Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)   Sep 13 2018 22:16:01   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

    Tuesday, April 16, 2019 2:18 PM
  • Thanks for your fast responses. 

    I'll try the options for replacing now!

    Tuesday, April 16, 2019 2:29 PM
  • Hi

    Thanks the PARSENAME approach shaved 3 seconds off the query time versus the original SUBSTRING +  CHARINDEX 

    SELECT ISIN

          --, SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
      , PARSENAME(REPLACE(col.value('(id)[1]', 'VARCHAR(50)'), '(','.'),2) AS pureID
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value],
      GetDate()
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper

    • Marked as answer by TilleyTech Ltd Wednesday, April 17, 2019 10:49 AM
    Tuesday, April 16, 2019 3:04 PM
  • Hey

    So everything is working well and at great speed now from the C# API connection to the serverside db logic, the last script above is called from a thread on the C# at a configurable number of ms.

    I'm now working more closely with the original developer of the Excel suite and the final table the above script hits is actually used by lots of different scripts and apps thus we cannot insert constantly as I am doing with the above INSERT INTO SELECT ... from our 1..n XML.

    His current insert only inserts if the ISIN + FID doesn't exist, otherwise it updates.

    Mmmm...As I start working out how I'd do this I thought id also thread it on to here as you were so helpful before!

    Thanks in advance for your assistance.

    Leigh

    Monday, April 29, 2019 8:44 AM
  • Ah yes, memory triggered after some googling!

    I can use my above script, but SELECT..INTO a #table

    Then do an 

    UPDATE targetRealTable trt

    FROM #table tmp

    WHERE trt.isin = tmp.isin

    AND trt.fid = tmp.fid

    but any advice appreciated on the best approach to cater for this combined with the situation of new items appearing (ISIN + FID)

    Thanks in advance.

    Leigh

    Monday, April 29, 2019 1:19 PM
  • Ah

    I'd never used the MERGE command. Looks like this will do it! :)

    Monday, April 29, 2019 1:56 PM
  • I think  my logic is OK, but I was expecting an INSERT on first run, as target table is empty....0 rows though...


    --1) Get ourselves just behind the latest updates
    DECLARE @lower VARCHAR(24)
    DECLARE @upper VARCHAR(24)
    DECLARE @ms VARCHAR(4)
    DECLARE @secondsForDateRange INT

    SELECT @secondsForDateRange = -10
    SELECT @ms = '.000'

    --Get max datetime in table containing XML column the sit 1min behind it
    --Was 1 min behind, try 30 seconds
    SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
    FROM ArtxfeedXmlInfo (NOLOCK)

    --2) Insert into the marketdata table

    SET QUOTED_IDENTIFIER ON

    SELECT * INTO #tempFields
    FROM(
    SELECT ISIN
          --, SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
      , PARSENAME(REPLACE(col.value('(id)[1]', 'VARCHAR(50)'), '(','.'),2) AS fid
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value],
      GetDate() As 'Now'
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo (NOLOCK)) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper) as xmlToFields

    MERGE marketdatatest mdt
    USING #tempFields tf
    ON mdt.isin = tf.isin
    AND mdt.fid = tf.fid
    WHEN MATCHED THEN
    UPDATE
    SET mdt.value = tf.value;

    drop table #tempFields

    Monday, April 29, 2019 2:16 PM
  • haha I just found my answer...

    WHEN NOT MATCHED INSERT...etc... :)

    Will try now..

    Monday, April 29, 2019 2:38 PM
  • Yes, for anyone in terested:

    --1) Get ourselves just behind the latest updates

    DECLARE @lower VARCHAR(24)
    DECLARE @upper VARCHAR(24)
    DECLARE @ms VARCHAR(4)
    DECLARE @secondsForDateRange INT

    SELECT @secondsForDateRange = -10
    SELECT @ms = '.000'

    --Get max datetime in table containing XML column the sit 1min behind it
    --Was 1 min behind, try 30 seconds
    SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
    FROM ArtxfeedXmlInfo (NOLOCK)

    --2) Insert into the marketdata table

    SET QUOTED_IDENTIFIER ON

    SELECT * INTO #tempFields
    FROM(
    SELECT ISIN
          --, SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
      , PARSENAME(REPLACE(col.value('(id)[1]', 'VARCHAR(50)'), '(','.'),2) AS fid
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value],
      GetDate() As 'Now'
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo (NOLOCK)) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper) as xmlToFields

    MERGE marketdatatest mdt
    USING #tempFields tf
    ON mdt.isin = tf.isin
    AND mdt.fid = tf.fid
    WHEN MATCHED THEN
    UPDATE
    SET mdt.value = tf.value
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (isin, fid, value, timestamp)
    VALUES (tf.isin, tf.fid, tf.value, tf.Now);

    drop table #tempFields

    Monday, April 29, 2019 2:41 PM
  • Ah, actually one more tweak needed.

    Because our scan of 1..n XML to FID | Value pulls in more than one version FID | Value, whch in reality it shouldn't, it has raised the last thing to tweak.

    So our scan brings in

    12345678911, FID1, Value, 13:01:02:03

    12345678911, FID1, Value, 13:01:02:02

    it means when we merge, we have dupe ISIN + FID

    So, im now in the middle of tweaking this to only dump the last / max /latest value in the #temp table.

    But as it is such a convoluted query, it's tricky (maybe im tired haha)

    So as you seem faster at this, and assisted me originally, what is your advice on editing this so it only brings latest distinct isin, fid, value

    SELECT * INTO #tempFields
    FROM(
    SELECT ISIN
          --, SUBSTRING(col.value('(id)[1]','VARCHAR(MAX)'),1, CHARINDEX('(' , col.value('(id)[1]','VARCHAR(MAX)'))-1) AS ID
      , PARSENAME(REPLACE(col.value('(id)[1]', 'VARCHAR(50)'), '(','.'),2) AS fid
          , col.value('(field)[1]','VARCHAR(MAX)') AS [Value],
      GetDate() As 'Now'
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo (NOLOCK)) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper) as xmlToFields

    Thanks in advance!

    Leigh

    Monday, April 29, 2019 3:44 PM
  • Hi TilleyTech Ltd,

    Here is a solution for you that removes duplicates:

    ;WITH rs AS
    (
    SELECT ISIN
    	  , PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) AS fieldID -- the best approach
          , col.value('(field)[1]','VARCHAR(50)') AS fieldValue
    	  , GETDATE() As [Now]
    FROM (SELECT ISIN, [data].query('<root>
    			{
    			  for $field in /CustomRecord/field
    			  return <row>
    						<id>{data($field/@ID)}</id>
    						<field>{data($field)}</field>
    					</row>
    			}</root>') AS dataXML
    		FROM @ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    )
    , noDuplicates AS
    (
    	SELECT rs.*
    		  , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fieldID ORDER BY [Now] DESC)
    	FROM rs
    )
    SELECT * /* INTO #tempFields */ -- uncomment when you are ready
    FROM noDuplicates
    WHERE rn = 1;
    Monday, April 29, 2019 4:50 PM
  • Thanks so much for your prompt, professional and excellent replies! I will plug this in for a try now
    Tuesday, April 30, 2019 8:41 AM
  • Actually as the original XML table is being hammered constantly with data, and growing (!!!), I do need to sit a few seconds behind and limit the scope. So this script is (housed in a stored proc) fired from C# as a separate thread



    DECLARE @lower VARCHAR(24)
    DECLARE @upper VARCHAR(24)
    DECLARE @ms VARCHAR(4)
    DECLARE @secondsForDateRange INT

    SELECT @secondsForDateRange = -3
    SELECT @ms = '.000'

    --Get max datetime in table containing XML column the sit 1min behind it
    --Was 1 min behind, try 30 seconds
    SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
    FROM ArtxfeedXmlInfo (NOLOCK)

    ;WITH rs AS
    (
    SELECT ISIN
      , PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) AS fid -- the best approach
          , col.value('(field)[1]','VARCHAR(50)') AS [value]
      , [Datetime]
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper)
    , noDuplicates AS
    (
    SELECT rs.*
      , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fid ORDER BY [Datetime] DESC)
    FROM rs
    )
    SELECT * INTO #tempFields  -- uncomment when you are ready
    FROM noDuplicates
    WHERE rn = 1;

    MERGE marketdatatest mdt
    USING #tempFields tf
    ON mdt.isin = tf.isin
    AND mdt.fid = tf.fid
    WHEN MATCHED THEN
    UPDATE
    SET mdt.value = tf.value, mdt.timestamp = tf.[datetime]
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (isin, fid, value, timestamp)
    VALUES (tf.isin, tf.fid, tf.value, tf.Datetime);


    DROP TABLE #tempFields

    Thanks for your help in assisting me to get to this point.

    Tuesday, April 30, 2019 11:24 AM
  • Hey

    I trust you're well! :)

    What would be the best way to approach altering/enhancing this code in order to replace the fid value only if the @Source attribute of ARTXPriceRecord is a certain value?

    ;WITH rs AS
    (
    SELECT ISIN
      , PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) AS fid -- the best approach
          , col.value('(field)[1]','VARCHAR(50)') AS [value]
      , [Datetime]
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper)
    , noDuplicates AS
    (
    SELECT rs.*
      , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fid ORDER BY [Datetime] DESC)
    FROM rs

    For example:

    <ARTXPriceFeedRecord Source="LookingForMe" Ticker="123456789112">
      <field ID="A_FIELD_NAME">1.00</field>
      <field ID="ANOTHER_FIELD_NAME">2.54</field>
      <field ID="ANOTHER_FIELD_NAME">4.56</field>
    </ARTXPriceFeedRecord>

    we want to change the fieldname A_FIELD_NAME only if the Source is "LookingForMe".

    Would I do it in the inner SQL/for 1..n fields bit, or amend this and also select Source on outer and use SQL conditional?

    Thanks in advance for your expert and informed opinion!

    Leigh

    Wednesday, May 8, 2019 8:07 AM
  • Hi TilleyTech Ltd,

    You can filter it out starting at the XML level.

    Please see below:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="MKT_MKR_NM(214)">UT1NGJ_MT</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="LookingForMe" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">77</field>
    </CustomRecord>');
    
    -- just to see it, XQuery FLWOR expression to re-shape the XML
    SELECT ISIN, [data].query('<root>
        {
          for $field in /CustomRecord[@Source="LookingForMe"]/field
          return <row>
    				<id>{data($field/@ID)}</id>
    				<field>{data($field)}</field>
    			</row>
    }</root>') AS dataXML
    FROM @ArtxfeedXmlInfo;
    Wednesday, May 8, 2019 2:41 PM
  • Hey thanks for your super fast answer.

    I need to do what I was originally doing, with no filter but if the code sees items with attribute @Source="LookingForMe" it should rename the FID from what it is to an alias.

    Maybe I should bring the Source back into the outer query and check it?

    Wednesday, May 8, 2019 2:51 PM
  • Hi TilleyTech Ltd,

    Here is how to expose the @Source attribute:

    DECLARE @ArtxfeedXmlInfo TABLE (ISIN INT, [data] XML);
    INSERT INTO  @ArtxfeedXmlInfo
    VALUES (1, '<CustomRecord Source="A NAME" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_O_ASKQTY(-18116)">1.0</field>
       <field ID="ION_O_BIDQTY(-18114)">1.0</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKASSETSWAPSPREAD(-18002)">0</field>
       <field ID="BID(22)">0</field>
       <field ID="ASK(25)">0</field>
       <field ID="YIELD(35)">0</field>
       <field ID="MID_PRICE(134)">0</field>
       <field ID="MKT_MKR_NM(214)">UT1NGJ_MT</field>
       <field ID="BID_YIELD(362)">0</field>
       <field ID="ASK_YIELD(363)">0</field>
       <field ID="COMMENTTXT(553)">fl</field>
       <field ID="BUYMARGIN(777)">0</field>
       <field ID="SELLMARGIN(779)">0</field>
       <field ID="GEN_VAL1(996)">0</field>
       <field ID="GEN_VAL2(997)">0</field>
       <field ID="GEN_VAL3(998)">0</field>
       <field ID="GEN_VAL4(999)">0</field>
       <field ID="ASK_SPREAD(3296)">101</field>
       <field ID="BID_SPREAD(3303)">100.25</field>
    </CustomRecord>')
    , (2, '<CustomRecord Source="LookingForMe" Ticker="A Name">
       <field ID="ION_COMMENT2(-18149)">order basis only</field>
       <field ID="ION_DESC(-18099)">LAMON 7.625 PERPETUAL -33</field>
       <field ID="ION_BIDASSETSWAPSPREAD(-18009)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">0</field>
       <field ID="ION_ASKSPREAD2(-18005)">77</field>
    </CustomRecord>');
    
    -- just to see it, XQuery FLWOR expression to re-shape the XML
    SELECT ISIN, [data].query('<root>
        {
          for $field in /CustomRecord/field
          return <row>
    				<id>{data($field/@ID)}</id>
    				<field>{data($field)}</field>
    				<source>{data(CustomRecord/@Source)}</source>
    			</row>
    }</root>') AS dataXML
    FROM @ArtxfeedXmlInfo;

    Wednesday, May 8, 2019 3:15 PM
  • hey

    Thanks as always for your fast and accurate replies my friend. I'll plug it in to try.

    Wednesday, May 8, 2019 3:24 PM
  • Morning 

    I trust you're well!

    What small thing have I mised here?

    SELECT ISIN,
    col.value('(Source)[1]','VARCHAR(50)') AS [Source]
      , PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) AS fid -- the best approach
          , col.value('(field)[1]','VARCHAR(50)') AS [value]
      , [Datetime]
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    <source>{data(ARTXPriceFeedRecord/@Source)}</source>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper)

    Source is NULL.

    Sorry to ask; busy here on my own so im covering multiple things and your asssistance greatly appreciated!

    Leigh

    Thursday, May 9, 2019 8:36 AM
  • Pleasre ignore; case sensitive and I had 'S' rather than 's'. :)
    Thursday, May 9, 2019 9:01 AM
  • Hi

    I found out I need to add some calculated rows now!!?!

    What would you say is the best way to add new rows into the 'tempFields table based on some values within it?!

    Basically, for every distinct ISIN it will have a SEG_TEXT value (4 possible values in here).

    E.g. MID value of two separate rows contained for every ISIN.

    Then after that some more calculated rows....I've started it and there are comments here for what to do next.

    You know more modern SQL than me so I'm sure you'll have great input!

    Many thanks in advance for your pro assistance!


    DECLARE @lower VARCHAR(24)
    DECLARE @upper VARCHAR(24)
    DECLARE @ms VARCHAR(4)
    DECLARE @secondsForDateRange INT
    DECLARE @fieldToAlias VARCHAR(50)
    DECLARE @SourceToCheck VARCHAR(50)

    DROP TABLE IF EXISTS #tempYesterday;
    DROP TABLE IF EXISTS #tempFields;
    DROP TABLE IF EXISTS #aliases;

    SELECT @secondsForDateRange = -15
    SELECT @ms = '.000'
    SELECT @fieldToAlias = 'A_FIELD_1'
    SELECT @SourceToCheck = 'SOURCE_1'

    --TO DO - IN FUTURE WE MAY WANT TO MAKE THIS BIT/BELOW ENTIRELY GENERIC BUT AT MOMENT
    --WE SET THE ONE FIELD ABOVE. WE'D LOOK THROUGH THIS TABLE OF ALL FIELDS RATHER THAN ONE
    --BRING THEM ALL IN AS FASTER IN TEMP / MEM TABLE
    SELECT [name], alias INTO #aliases FROM [Runs].dbo.ArtxFieldsToDecode WHERE [name] = @fieldToAlias

    --Business day ago
    SELECT ISIN, 
    SEG_TEXT, 
    COALESCE((CAST(CLOSE_BID AS FLOAT) + CAST(CLOSE_ASK AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_AVG,
    COALESCE((CAST(CLOSE_ASK_PRC AS FLOAT) + CAST(CLOSE_BID_PRC AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_PRICE_AVG,
    COALESCE((CAST(YIELD AS FLOAT) + CAST(YIELD AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_YIELD_AVG
    INTO #tempYesterday
    FROM marketdatahistory
    WHERE DATE = (SELECT CONVERT(VARCHAR(10), DATEADD(DAY,
                        (CASE DATENAME(WEEKDAY, convert(date,GETDATE()))
    WHEN 'Sunday' THEN -2 
                            WHEN 'Monday' THEN -3 
                            ELSE -1
                          END),
                          CONVERT(DATE, GETDATE()))) + ' 00:00:00.000');

    --TODO business week ago
    -- USE WHERE DATE = (SELECT CONVERT(VARCHAR(10), DATEADD(DAY,
    --                    (CASE DATENAME(WEEKDAY, convert(date,GETDATE()))
    -- WHEN 'Sunday' THEN -8 
    --                        WHEN 'Monday' THEN -9 
    --                        ELSE -7
    --                      END),
    --                      CONVERT(DATE, GETDATE()))) + ' 00:00:00.000');

    --TODO business last month end


    --Get max datetime in table containing XML column the sit 1min behind it
    --Was 1 min behind, try 30 seconds
    SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
    FROM ArtxfeedXmlInfo (NOLOCK)

    ;WITH rs AS
    (
    SELECT ISIN,
    CASE WHEN CHARINDEX(@SourceToCheck,col.value('(source)[1]','VARCHAR(50)') ) > 0 THEN
    CASE WHEN PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) = @fieldToAlias THEN
    (SELECT alias FROM #aliases WHERE [Name] = @fieldToAlias)
    ELSE
    PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2)
    END
    ELSE 
    PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) 
      END AS fid
          , col.value('(field)[1]','VARCHAR(50)') AS [value]
      , [Datetime]
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    <source>{data(ARTXPriceFeedRecord/@Source)}</source>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper)
    , noDuplicates AS
    (
    SELECT rs.*
      , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fid ORDER BY [Datetime] DESC)
    FROM rs
    )


    SELECT * INTO #tempFields  
    FROM noDuplicates
    WHERE rn = 1;

    --ADD CALCULATED FIELDS
    --We have to add the MID_SPREAD as a new FID and value based on ASK_SPREAD and BID_SPREAD in #tempFields
    --Then we have to add the calculated fields based on SEG_TEXT value E.g. if SEG_TEXT = 1, then MID_SPREAD - YESTERDAY_CLOSE_AVG etc

    --MERGE FOLLOWS HERE


    Friday, May 10, 2019 3:56 PM
  • I just plugged in the MID_SPREAD, but it's understandably heavy as it'll scan entire #tempFields in order to plug back in the AVG of BID and ASK.

    I now have to create 12 derived fields too...your more advanced SQL advice would be appreciated.

    DECLARE @lower VARCHAR(24)
    DECLARE @upper VARCHAR(24)
    DECLARE @ms VARCHAR(4)
    DECLARE @secondsForDateRange INT
    DECLARE @fieldToAlias VARCHAR(50)
    DECLARE @SourceToCheck VARCHAR(50)
    DECLARE @lastBusinessDay VARCHAR(23)

    DROP TABLE IF EXISTS #tempYesterday;
    DROP TABLE IF EXISTS #tempFields;
    DROP TABLE IF EXISTS #aliases;

    SELECT @secondsForDateRange = -15
    SELECT @ms = '.000'
    SELECT @fieldToAlias = 'A_PRICE_1'
    SELECT @SourceToCheck = 'MUREX'

    SELECT @lastBusinessDay = (CONVERT(VARCHAR(10), DATEADD(DAY,
                        (CASE DATENAME(WEEKDAY, convert(date,GETDATE()))
    WHEN 'Sunday' THEN -2 
                            WHEN 'Monday' THEN -3 
                            ELSE -1
                          END),
                          CONVERT(DATE, GETDATE()))) + ' 00:00:00.000')

    --PRINT @lastBusinessDay

    --TO DO - IN FUTURE WE MAY WANT TO MAKE THIS BIT/BELOW ENTIRELY GENERIC BUT AT MOMENT
    --WE SET THE ONE FIELD ABOVE. WE'D LOOK THROUGH THIS TABLE OF ALL FIELDS RATHER THAN ONE
    --BRING THEM ALL IN AS FASTER IN TEMP / MEM TABLE
    SELECT [name], alias INTO #aliases FROM [Runs].dbo.ArtxFieldsToDecode WHERE [name] = @fieldToAlias

    --Business day ago
    SELECT ISIN, 
    SEG_TEXT, 
    COALESCE((CAST(CLOSE_BID AS FLOAT) + CAST(CLOSE_ASK AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_AVG,
    COALESCE((CAST(CLOSE_ASK_PRC AS FLOAT) + CAST(CLOSE_BID_PRC AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_PRICE_AVG,
    COALESCE((CAST(YIELD AS FLOAT) + CAST(YIELD AS FLOAT)) / 2, 0) AS YESTERDAY_CLOSE_YIELD_AVG
    INTO #tempYesterday
    FROM marketdatahistory
    WHERE DATE = @lastBusinessDay;

    TODO business week ago
     USE WHERE DATE = (SELECT CONVERT(VARCHAR(10), DATEADD(DAY,
                        (CASE DATENAME(WEEKDAY, convert(date,GETDATE()))
    WHEN 'Sunday' THEN -8 
                            WHEN 'Monday' THEN -9 
                            ELSE -7
                          END),
                          CONVERT(DATE, GETDATE()))) + ' 00:00:00.000');

    --TODO business last month end


    --Get max datetime in table containing XML column the sit 1min behind it
    --Was 1 min behind, try 30 seconds
    SELECT @lower = FORMAT(DATEADD(SECOND,@secondsForDateRange, MAX([DATETIME])), 'dd MMM yyyy HH:mm:ss') + @ms, @upper = FORMAT(MAX([DATETIME]), 'dd MMM yyyy HH:mm:ss')  + '.000' + @ms
    FROM ArtxfeedXmlInfo (NOLOCK)

    ;WITH rs AS
    (
    SELECT ISIN,
    CASE WHEN CHARINDEX(@SourceToCheck,col.value('(source)[1]','VARCHAR(50)') ) > 0 THEN
    CASE WHEN PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) = @fieldToAlias THEN
    (SELECT alias FROM #aliases WHERE [Name] = @fieldToAlias)
    ELSE
    PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2)
    END
    ELSE 
    PARSENAME(REPLACE(col.value('(id)[1]','VARCHAR(50)'), '(', '.'), 2) 
      END AS fid
          , col.value('(field)[1]','VARCHAR(50)') AS [value]
      , [Datetime]
    FROM (SELECT ISIN, Datetime, [data].query('<root>
    {
      for $field in /ARTXPriceFeedRecord/field
      return <row>
    <id>{data($field/@ID)}</id>
    <field>{data($field)}</field>
    <source>{data(ARTXPriceFeedRecord/@Source)}</source>
    </row>
    }</root>') AS dataXML
    FROM ArtxfeedXmlInfo) tbl
    CROSS APPLY tbl.dataXML.nodes('/root/row') AS tab(col)
    WHERE [Datetime] BETWEEN @lower AND @upper)
    , noDuplicates AS
    (
    SELECT rs.*
      , rn = ROW_NUMBER() OVER(PARTITION BY ISIN, fid ORDER BY [Datetime] DESC)
    FROM rs
    )

    SELECT ISIN, FID, [Value], [Datetime] INTO #tempFields  
    FROM noDuplicates
    WHERE rn = 1;

    --ADD CALCULATED FIELDS

    --ADD MID

    INSERT INTO #tempFields
    SELECT DISTINCT ISIN, 
    'MID_SPREAD' AS FID,
    AVG(CAST(VALUE AS FLOAT)) AS [Value],
    GetDate() AS Now
    FROM #tempFields
    WHERE FID IN('ASK_SPREAD', 'BID_SPREAD')
    GROUP BY ISIN

    --ADD CALCULATED FIELDS THAT RELY ON MID

    --MERGE FOLLOWS HERE

    Monday, May 13, 2019 12:40 PM