question

ivric avatar image
ivric asked

Reading repeating tags using open xml or xml functions SQL

3342 abc 5342 xyz | Dig_Isprim | Code | Name | |-------------|--------|--------| | True | 3342 | abc | |-------------|--------|--------| | False | 5342 | xyz |
sqlxmlsql server 2012sql server 2008openxml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
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 = ' 3342 abc 5342 xyz '; 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.