?
Solved

TSQL update statement from XML

Posted on 2014-09-29
7
Medium Priority
?
126 Views
Last Modified: 2014-09-29
Hello,

Is there a way to systimatically travese an xml document and update an underlying table?

I have a table called DocumentData defined as

TABLE [dbo].[DocumentData](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [FieldName] [nvarchar](35) NULL,
      [FieldValue] [nvarchar](100) NULL
)

I would like to update the table (only update) by traversing the following xml (the structure is always the same, just different numbers of nodes) and using the node names to identify the FieldName value, and update the FieldValue with the node value.

Note that the Id node is also part of the where statement because it identifies the row.

If i was to write it long hand i would expect a series of statements like this:

update DocumentData set FieldValue = '19' where id = 5 and FieldName = 'Alder'
update DocumentData set FieldValue = 'VC Skurup' where id = 5 and FieldName = 'Enhet'
update DocumentData set FieldValue  = 'This is text 333333.' where id = 5 and FieldName = 'formulardatum'


<BRUM-registret>
  <TestForm1>
    <Id>5</Id>
    <Alder changed="0">19</Alder>
    <Enhet changed="0">VC Skurup</Enhet>
    <formulardatum changed="0">This is text 333333.</formulardatum>
    <kon changed="0">1</kon>
    <Namn changed="0">William Tofält</Namn>
    <projektidnummer changed="0">210729</projektidnummer>
    <Q1 changed="0">500</Q1>
    <Q10 changed="0">6</Q10>
    <Q2 changed="0">8</Q2>
    <Q3 changed="0">4</Q3>
    <Q4 changed="0">6</Q4>
    <Q5 changed="0">6</Q5>
    <Q6 changed="0">4</Q6>
    <Q7 changed="0">7</Q7>
    <Q8 changed="0">9</Q8>
    <Q9 changed="0">10</Q9>
    <Sjukgymnast changed="0">Elisabet Håkansson</Sjukgymnast>
    <Summa changed="0" />
  </TestForm1>
</BRUM-registret>

Open in new window


However there must be a way to traverse the document, extract the data and do the updates`?
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40349668
You need to parse it using local-name(). E.g.

DECLARE @Xml XML = N'
<BRUM-registret>
	<TestForm1>
		<Id>5</Id>
		<Alder changed="0">19</Alder>
		<Enhet changed="0">VC Skurup</Enhet>
		<formulardatum changed="0">This is text 333333.</formulardatum>
		<kon changed="0">1</kon>
		<Namn changed="0">William Tofält</Namn>
		<projektidnummer changed="0">210729</projektidnummer>
		<Q1 changed="0">500</Q1>
		<Q10 changed="0">6</Q10>
		<Q2 changed="0">8</Q2>
		<Q3 changed="0">4</Q3>
		<Q4 changed="0">6</Q4>
		<Q5 changed="0">6</Q5>
		<Q6 changed="0">4</Q6>
		<Q7 changed="0">7</Q7>
		<Q8 changed="0">9</Q8>
		<Q9 changed="0">10</Q9>
		<Sjukgymnast changed="0">Elisabet Håkansson</Sjukgymnast>
		<Summa changed="0" />
	</TestForm1>
</BRUM-registret>
';

WITH    Data
          AS ( SELECT   TestForm1.value('Id[1]', 'INT') AS ID ,
                        KeyValue.value('local-name(.)', 'NVARCHAR(255)') AS [Key] ,
                        KeyValue.value('.', 'NVARCHAR(255)') AS [Value]
               FROM     @Xml.nodes('/BRUM-registret/TestForm1') A ( TestForm1 )
                        CROSS APPLY TestForm1.nodes('*[local-name()!="Id"]') B ( KeyValue )
             )
    UPDATE  DD
    SET     FieldValue = D.[Value]
    FROM    DocumentData DD
            INNER JOIN Data D ON DD.id = D.ID
                                 AND DD.FieldName = D.[Key];

Open in new window


But your structure has a little code smell. What does the ID stand for? Can TestForm1 only display one row? E.g.
<TestForm1 @Id="5">
	<Alder changed="0">19</Alder>		
</TestForm1>
<!-- or -->
<TestForm1>
	<Row Id="5">
		<Alder changed="0">19</Alder>		
	</Row>
</TestForm1>

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40349671
you can extract shorter using
declare @x xml = '<BRUM-registret>
  <TestForm1>
    <Id>5</Id>
    <Alder changed="0">19</Alder>
    <Enhet changed="0">VC Skurup</Enhet>
    <formulardatum changed="0">This is text 333333.</formulardatum>
    <kon changed="0">1</kon>
    <Namn changed="0">William Tofält</Namn>
    <projektidnummer changed="0">210729</projektidnummer>
    <Q1 changed="0">500</Q1>
    <Q10 changed="0">6</Q10>
    <Q2 changed="0">8</Q2>
    <Q3 changed="0">4</Q3>
    <Q4 changed="0">6</Q4>
    <Q5 changed="0">6</Q5>
    <Q6 changed="0">4</Q6>
    <Q7 changed="0">7</Q7>
    <Q8 changed="0">9</Q8>
    <Q9 changed="0">10</Q9>
    <Sjukgymnast changed="0">Elisabet Håkansson</Sjukgymnast>
    <Summa changed="0" />
  </TestForm1>
</BRUM-registret>'


select @x.value('(//Id)[1]', 'int') id
    ,  @x.value('(//Enhet)[1]', 'varchar(100)') Enhet

Open in new window

0
 

Author Comment

by:soozh
ID: 40349674
To answer:

"But your structure has a little code smell. What does the ID stand for? Can TestForm1 only display one row? E.g."

No the id and fieldname columns are the unique key.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Expert Comment

by:ste5an
ID: 40349676
btw, imho you can also filter on the changed attribute:

DECLARE @Xml XML = N'
<BRUM-registret>
	<TestForm1>
		<Id>5</Id>
		<Alder changed="1">19</Alder>
		<Enhet changed="1">VC Skurup</Enhet>
		<formulardatum changed="0">This is text 333333.</formulardatum>
		<kon changed="0">1</kon>
		<Namn changed="0">William Tofält</Namn>
		<projektidnummer changed="0">210729</projektidnummer>
		<Q1 changed="0">500</Q1>
		<Q10 changed="0">6</Q10>
		<Q2 changed="0">8</Q2>
		<Q3 changed="0">4</Q3>
		<Q4 changed="0">6</Q4>
		<Q5 changed="0">6</Q5>
		<Q6 changed="0">4</Q6>
		<Q7 changed="0">7</Q7>
		<Q8 changed="0">9</Q8>
		<Q9 changed="0">10</Q9>
		<Sjukgymnast changed="0">Elisabet Håkansson</Sjukgymnast>
		<Summa changed="0" />
	</TestForm1>
</BRUM-registret>
';

SELECT  TestForm1.value('Id[1]', 'INT') AS ID ,
        KeyValue.value('local-name(.)', 'NVARCHAR(255)') AS [Key] ,
        KeyValue.value('.', 'NVARCHAR(255)') AS [Value]
FROM    @Xml.nodes('/BRUM-registret/TestForm1') A ( TestForm1 )
        CROSS APPLY TestForm1.nodes('*[local-name()!="Id" and @changed!="0"]') B ( KeyValue )

Open in new window

0
 

Author Comment

by:soozh
ID: 40349706
I have tested your example ste5an but i find it does not update any rows in the database.

I was wondering if there was something with the inner join because i can see it extracts the correct data from the xml?

INNER JOIN Data D ON DD.id = D.ID
                                 AND DD.FieldName = D.[Key]
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40349747
The JOIN is under normal circumstances correct. Here's a complete working example:

 
DECLARE @DocumentData TABLE
    (
      [id] [INT] ,
      [FieldName] [NVARCHAR](35) ,
      [FieldValue] [NVARCHAR](100)
    );

INSERT  INTO @DocumentData
        ( id, FieldName )
VALUES  ( 5, N'Alder' ),
        ( 5, N'Enhet' ),
        ( 5, N'formulardatum' ),
        ( 6, N'Alder' ),
        ( 6, N'Enhet' ),
        ( 6, N'formulardatum' );

SELECT  *
FROM    @DocumentData DD;

DECLARE @Xml XML = N'
<BRUM-registret>
	<TestForm1>
		<Id>5</Id>
		<Alder changed="0">19</Alder>
		<Enhet changed="0">VC Skurup</Enhet>
		<formulardatum changed="0">This is text 333333.</formulardatum>
		<kon changed="0">1</kon>
		<Namn changed="0">William Tofält</Namn>
		<projektidnummer changed="0">210729</projektidnummer>
		<Q1 changed="0">500</Q1>
		<Q10 changed="0">6</Q10>
		<Q2 changed="0">8</Q2>
		<Q3 changed="0">4</Q3>
		<Q4 changed="0">6</Q4>
		<Q5 changed="0">6</Q5>
		<Q6 changed="0">4</Q6>
		<Q7 changed="0">7</Q7>
		<Q8 changed="0">9</Q8>
		<Q9 changed="0">10</Q9>
		<Sjukgymnast changed="0">Elisabet Håkansson</Sjukgymnast>
		<Summa changed="0" />
	</TestForm1>
</BRUM-registret>
';

WITH    Data
          AS ( SELECT   TestForm1.value('Id[1]', 'INT') AS ID ,
                        KeyValue.value('local-name(.)', 'NVARCHAR(255)') AS [Key] ,
                        KeyValue.value('.', 'NVARCHAR(255)') AS [Value]
               FROM     @Xml.nodes('/BRUM-registret/TestForm1') A ( TestForm1 )
                        CROSS APPLY TestForm1.nodes('*[local-name()!="Id"]') B ( KeyValue )
             )
    UPDATE  DD
    SET     FieldValue = D.[Value]
    FROM    @DocumentData DD
            INNER JOIN Data D ON DD.id = D.ID
                                 AND DD.FieldName = D.[Key];

SELECT  *
FROM    @DocumentData DD;

Open in new window


So, check your data in the table. Maybe a typo or the ID is wrong.
0
 

Author Comment

by:soozh
ID: 40349818
yep  a typo, or should i say i confused two column names....
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question