Query Optimization while updating XML String in SQL Server?

Question

I'm working with XML string shown below.

I have to update the XML string as follows:

If the XML string contains 1000 records or more, it kills the query
If the XML string contains < 1000 records, let it continue.

How can I do this?

example data

<root xmlns:json="http://james.newtonking.com/projects/json">
  <row json:Array="true" RowNumber="1">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.2" />
    <Column json:Array="true" Name="HourTo" Value="13.3" />
    <Column json:Array="true" Name="Rate" Value="0.895" />
  </row>
  <row json:Array="true" RowNumber="2">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.3" />
    <Column json:Array="true" Name="HourTo" Value="13.4" />
    <Column json:Array="true" Name="Rate" Value="0.907" />
  </row>
</root>

Temp table creation

    CREATE TABLE #xmltable(
                           Id INT Identity (1,1)  PRIMARY KEY CLUSTERED,
                           DataValue XML
                          );


    CREATE PRIMARY XML INDEX indexratesheet ON #xmltable
                    (
                      DataValue 
                     )

Inserting data into table

    INSERT INTO (DataValue ) VALUES(TheXMLfromAbove)

updating the XML string in the table

    DECLARE @i INT 1
    WHILE(@i<=1000)
    BEGIN
         UPDATE #xmltable SET DataValue.modify('insert <Column Name="ValidationComments" Value="{sql:variable("@validationcomments")}"></Column>
                              into (/root/row[@i=sql:variable("@i")])[1]')
   SET @i=@i+1
   END

This data looks very much like better-to-store-in-a-table, doesn't it? Is there a good reason to keep this in XML? I would store this in a normal table and create the XML when I need it... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 18, 2017 at 8:15
Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Since you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 16, 2017 at 11:37

Gottfried Lesigang · Accepted Answer · 2017-01-18 09:40:05Z

2

If you have to keep this with XML it should be much faster to shred the whole XML into a derivedTable and re-build it from scratch.

Try this:

CREATE TABLE #xmltable(
                           Id INT Identity (1,1)  PRIMARY KEY CLUSTERED,
                           DataValue XML
                          );


    CREATE PRIMARY XML INDEX indexratesheet ON #xmltable
                    (
                      DataValue 
                     );

--Your test XML

    INSERT INTO #xmltable (DataValue ) VALUES(N'<root xmlns:json="http://james.newtonking.com/projects/json">
  <row json:Array="true" RowNumber="1">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.2" />
    <Column json:Array="true" Name="HourTo" Value="13.3" />
    <Column json:Array="true" Name="Rate" Value="0.895" />
  </row>
  <row json:Array="true" RowNumber="2">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.3" />
    <Column json:Array="true" Name="HourTo" Value="13.4" />
    <Column json:Array="true" Name="Rate" Value="0.907" />
  </row>
</root>');

--The query to shred it

SELECT r.value(N'@RowNumber','int') AS RowNumber
      ,r.value(N'(Column[@Name="Number"]/@Value)[1]','int') AS Number
      ,r.value(N'(Column[@Name="HourFrom"]/@Value)[1]','decimal(10,4)') AS HourFrom
      ,r.value(N'(Column[@Name="HourTo"]/@Value)[1]','decimal(10,4)') AS HourTo
      ,r.value(N'(Column[@Name="Rate"]/@Value)[1]','decimal(10,4)') AS Rate
INTO #derivedTable
FROM #xmltable AS t
CROSS APPLY t.DataValue.nodes(N'/root/row') AS A(r);

--The query to re-build it

WITH XMLNAMESPACES('http://james.newtonking.com/projects/json' AS json)
SELECT 'true' AS [@json:Array]
      ,t.RowNumber AS [@RowNumber]
      ,'true' AS [Column/@json:Array]
      ,'Number' AS [Column/@Name]
      ,t.Number AS [Column/@Value]
      ,''
      ,'true' AS [Column/@json:Array]
      ,'HourFrom' AS [Column/@Name]
      ,t.HourFrom AS [Column/@Value]
      ,''
      ,'true' AS [Column/@json:Array]
      ,'HourTo' AS [Column/@Name]
      ,t.HourTo AS [Column/@Value]
      ,''
      ,'true' AS [Column/@json:Array]
      ,'Rate' AS [Column/@Name]
      ,t.Rate AS [Column/@Value]

      ,''
      ,'ValidationComments' AS [Column/@Name]
      ,'SomeValue' AS [Column/@Value]
FROM #derivedTable AS t
FOR XML PATH('row'),ROOT('root');

--Clean up (carefull with real data!)

GO

DROP TABLE #derivedTable;
DROP TABLE #xmltable

This is the result

<root xmlns:json="http://james.newtonking.com/projects/json">
  <row json:Array="true" RowNumber="1">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.2000" />
    <Column json:Array="true" Name="HourTo" Value="13.3000" />
    <Column json:Array="true" Name="Rate" Value="0.8950" />
    <Column Name="ValidationComments" Value="SomeValue" />
  </row>
  <row json:Array="true" RowNumber="2">
    <Column json:Array="true" Name="Number" Value="1" />
    <Column json:Array="true" Name="HourFrom" Value="13.3000" />
    <Column json:Array="true" Name="HourTo" Value="13.4000" />
    <Column json:Array="true" Name="Rate" Value="0.9070" />
    <Column Name="ValidationComments" Value="SomeValue" />
  </row>
</root>

UPDATE

Try this query, it will work for all different column lists, but it will repeat the namespace declaration. This is not wrong, but very annoying. At the moment I do not have the time to think about a hack. Let me know, if this works for you.

WITH XMLNAMESPACES('http://james.newtonking.com/projects/json' AS json)
,CTE AS
(
    SELECT r.value(N'@RowNumber','int') AS RowNumber
          ,r.query('./*') AS TheContent
    FROM #xmltable AS t
    CROSS APPLY t.DataValue.nodes(N'/root/row') AS A(r)
)
SELECT CTE.TheContent AS [*]
      ,'ValidationComments' AS [Column/@Name]
      ,'SomeValue' AS [Column/@Value]
FROM CTE
FOR XML PATH('row'),ROOT('root')

edited Jan 18, 2017 at 9:40

answered Jan 18, 2017 at 8:43

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Phani Rao Over a year ago

In below xml string only root node and row node is static column nodes in row node are dynamic how can i achieve from this as you suggests <row json:Array="true" RowNumber="2"> <Column json:Array="true" Name="Number" Value="1" /> <Column json:Array="true" Name="HourFrom" Value="13.3000" /> <Column json:Array="true" Name="HourTo" Value="13.4000" /> <Column json:Array="true" Name="Rate" Value="0.9070" /> <Column Name="ValidationComments" Value="SomeValue" /> </row>

Gottfried Lesigang Over a year ago

@PhaniRao What do you want to tell me with this XML fragment?

Phani Rao Over a year ago

please now check the comment once am waiting for your reply

Gottfried Lesigang Over a year ago

@PhaniRao Do I understand this correctly: The content within <root><row> is dynamic (different columns)?

Gottfried Lesigang Over a year ago

@PhaniRao One more question: Is the json:Array="true" really needed? It would be easier to pick it out, as it seems to be the same all over (due to namespace issues...)

|

Collectives™ on Stack Overflow

Query Optimization while updating XML String in SQL Server?

1 Answer 1

UPDATE

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related