x

Reading repeating tags using open xml or xml functions SQL

 <Dig IsPrim="True">
     <Code>3342</Code>
     <Name>abc</Name>
 </Dig>
 <Dig IsPrim="False">
     <Code>5342</Code>
     <Name>xyz</Name>
 </Dig>
  



| Dig_Isprim | Code | Name |

|-------------|--------|--------|

| True | 3342 | abc |

|-------------|--------|--------|

| False | 5342 | xyz |

more ▼

asked Sep 11 at 01:18 AM in Default

avatar image

ivric
0

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

When querying XML data, the most important part is to figure out which node to pick to represent the row of data. In this case, the XML is very simple, and the node representing a row is the node named Dig. From this node, what needs to be selected for each row is the following:

  1. The value of the attribute named IsPrim

  2. The inner text of the child node named Code

  3. The inner text of the child node named Name

The select statement (which includes the declaration/setting of the sample data) may look like this:

 -- sample xml data
 declare @x xml = '
     <Dig IsPrim="True">
         <Code>3342</Code>
         <Name>abc</Name>
     </Dig>
     <Dig IsPrim="False">
         <Code>5342</Code>
         <Name>xyz</Name>
     </Dig>';
 
 select
     x.value('@IsPrim[1]', 'varchar(5)') Dig_IsPrim,
     x.value('Code[1]', 'int') Code,
     x.value('Name[1]', 'varchar(10)') [Name]
     from @x.nodes('Dig') r(x);

Based on the XML in question, this query produces the following result:

 Dig_IsPrim Code        Name
 ---------- ----------- ----------
 True       3342        abc
 False      5342        xyz

Hope this helps.

Oleg

more ▼

answered Sep 11 at 01:33 PM

avatar image

Oleg
18.9k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1149
x202
x131
x38
x5

asked: Sep 11 at 01:18 AM

Seen: 16 times

Last Updated: Sep 11 at 01:33 PM

Copyright 2017 Redgate Software. Privacy Policy