Solved

TSQL update statement from XML

Posted on 2014-09-29
7
122 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 34

Accepted Solution

by:
ste5an earned 500 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 34

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 34

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
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: …

688 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