how to update or query xml with xmlns attributes

Question

say my xml doc is this

<root xmlns="http://www.w3.org/2001/XMLSchema-instance">
 <parent prop="1">
  <child>
   <field name="1">
    <value1>abc</value1>
    <value2>cdf</value2>
   </field>
   <field name="2">
    <value1>efg</value1>
    <value2>hjk</value2>
   </field>
  </child>
 </parent>
 <parent2>
   <prop atrb="2">abc</prop>
 </parent2>
</root>

i have it a table newTable2 and xml datatyped column as xmlcol1

here is the query i worte

SELECT        xmlcol1.query('/root/parent/child/field/value1/text()') AS a
FROM            newTable2

this works when i remove the xmlns attribute if i put it back it does can anyone explain why is it so and how can i query for same keeping the xmlns attribute.

GriGrim · Accepted Answer · 2014-03-25 12:03:19Z

1

Try this:

;with xmlnamespaces (
    default 'http://www.w3.org/2001/XMLSchema-instance'
)
SELECT xmlcol1.query('/root/parent/child/field/value1/text()') AS a_query
    , xmlcol1.value('(/root/parent/child/field/value1/text())[1]', 'varchar(255)') AS a_value_1
    , xmlcol1.value('(/root/parent/child/field/value1/text())[2]', 'varchar(255)') AS a_value_2
FROM newTable2

answered Mar 25, 2014 at 12:03

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

goldsmit409 Over a year ago

thnx bdw what are the numbers in [] signify?

GriGrim Over a year ago

[1] says to take exactly first text value among all text values of nodes value1. In your document it is 'abc'. [2] says to take exactly second text value among all text values of nodes value1. In your document it is 'efg'.

GriGrim Over a year ago

Where do you update it? You just select. For example, you have an xml document as you mentioned above. What other xml document would you like to get after update?

goldsmit409 Over a year ago

it would be great if you could take a look here stackoverflow.com/questions/22643699/…

CRABOLO · Accepted Answer · 2014-09-06 16:02:16Z

0

never mind i found the answer i just need to use the ;WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema-instance') before the query

edited Sep 6, 2014 at 16:02

CRABOLO

8,82439 gold badges47 silver badges68 bronze badges

answered Mar 25, 2014 at 12:04

goldsmit409

4784 silver badges26 bronze badges

Collectives™ on Stack Overflow

how to update or query xml with xmlns attributes

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related