Solved

TSQL update statement from XML

Posted on 2014-09-29
7
119 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
  • 3
  • 3
7 Comments
 
LVL 33

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 142

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 33

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 33

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

770 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