Solved

TSQL update statement from XML

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

730 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