Query to XML. Tildes Error

Question

 

In the following query, the tildes cause errors. 

The XML is correct and I can not modify.

How I can fix it?

 

 

DECLARE @XmlDocument xml
SET @XmlDocument = 
'<?xml version="1.0" encoding="UTF-8"?> 
<metadata>
 <fields>
  <field name="priority" label="Nivel de importancia">
   <option value="1">á</option>
   <option value="2">é</option>
   <option value="3">b</option>
   <option value="4">í</option>
   <option value="5">o</option>
  </field>
 </fields>
</metadata>'
  
SELECT 
 label = T.Item.value('../@label', 'varchar (50)'),
    name = T.Item.value('../@name', 'varchar (50)'),
    optionValue = T.Item.value('@value', 'varchar (50)'),
    optionText = T.Item.value('.', 'varchar (50)')
FROM   @XmlDocument.nodes('metadata/fields/field/option') AS T(Item)

Thanks

Answer 1

Use the utf-16, i.e.UNICODE, check this:

DECLARE @XmlDocument xml
SET @XmlDocument =
N'<?xml version="1.0" encoding="utf-16"?>
<metadata>
 <fields>
  <field name="priority" label="Nivel de importancia">
   <option value="1">á</option>
   <option value="2">é</option>
   <option value="3">b</option>
   <option value="4">í</option>
   <option value="5">o</option>
  </field>
 </fields>
</metadata>'
 
SELECT
 label = T.Item.value('../@label', 'varchar (50)'),
    name = T.Item.value('../@name', 'varchar (50)'),
    optionValue = T.Item.value('@value', 'varchar (50)'),
    optionText = T.Item.value('.', 'varchar (50)')
FROM   @XmlDocument.nodes('metadata/fields/field/option') AS T(Item)

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

here is a workaround:

DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'<?xml version="1.0" encoding="UTF-16"?>
<metadata>
 <fields>
  <field name="priority" label="Nivel de importancia">
   <option value="1">á</option>
   <option value="2">é</option>
   <option value="3">b</option>
   <option value="4">í</option>
   <option value="5">o</option>
  </field>
 </fields>
</metadata>'
 
declare @x xml
set @x=@XmlDocument
 
SELECT
 label = T.Item.value('../@label', 'varchar (50)'),
    name = T.Item.value('../@name', 'varchar (50)'),
    optionValue = T.Item.value(N'@value', 'varchar (50)'),
    optionText = T.Item.value('.', 'varchar (50)')
FROM   @x.nodes('metadata/fields/field/option') AS T(Item)

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 3

As the OP has indicated they can't modify the XML, I'm not sure the UTF-16 suggestions will work.

Also, don't use the parent axis (..) as it is very expensive, particularly on larger XML documents.

Try this:

DECLARE @XmlDocument XML

SET @XmlDocument =
REPLACE( '<?xml version="1.0" encoding="utf-8"?>
<metadata>
 <fields>
  <field name="priority" label="Nivel de importancia">
   <option value="1">á</option>
   <option value="2">é</option>
   <option value="3">b</option>
   <option value="4">í</option>
   <option value="5">o</option>
  </field>
 </fields>
</metadata>', '<?xml version="1.0" encoding="utf-8"?>', '' )


SELECT
	label = f.c.value('@label', 'varchar (50)'),
	name = f.c.value('@name', 'varchar (50)'),
	optionValue = o.c.value('@value', 'varchar (50)'),
	optionText = o.c.value('.', 'varchar (50)')
FROM @XmlDocument.nodes('metadata/fields/field') AS f(c)
	CROSS APPLY f.c.nodes( 'option' ) o(c)

Answer 4

Thanks WBob for reminding me that, I do have a link that demonstrate that use of parent axis as bad but it just slipped off my mind.

However, I have read that every XML processor is bound to automatically support the UTF-8 and UTF-16 encoding , then why it does not work with variable declared as XML. Also I requested for some links to study the structure of XML in one of my recent post but I didn't got a single reply http://social.msdn.microsoft.com/Forums/en-US/sqlxml/thread/67c463e1-4435-4feb-9227-565ad1e3c593

---

Thanks and regards, Rishabh , Microsoft Community Contributor