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

    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

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