• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

TSQL update statement from XML

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
soozh
Asked:
soozh
  • 3
  • 3
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
soozhAuthor Commented:
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
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!

 
ste5anSenior DeveloperCommented:
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
 
soozhAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
soozhAuthor Commented:
yep  a typo, or should i say i confused two column names....
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now