Hi All, I am not able to fetch a value from XML

Question

Post reply

Hi All, I am not able to fetch a value from XML

shivu.1611-854900

SSC Rookie

Points: 43
More actions
August 5, 2014 at 5:56 am

#294015

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<Attributes><Attribute name="ABCD">50405</Attribute></Attributes>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT * FROM OPENXML (@DocHandle, '/Attributes/Attribute')
WITH (name varchar(100),
Value varchar(100) '@name/text()[1]' )
EXEC sp_xml_removedocument @DocHandle
THe result of the above query is
nameValue
ABCDABCD
How do we get the value 50405 from the above xml

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Recommend that you use the nodes() method

😎

DECLARE @XmlDocument XML = N'<Attributes><Attribute name="ABCD">50405</Attribute></Attributes>';

SELECT

ATTRIB.DATA.value('@name','VARCHAR(10)') AS name

,ATTRIB.DATA.value('.[1]','INT') AS AttribVAlue

FROM @Xmldocument.nodes('Attributes/Attribute') AS ATTRIB(DATA)

Results

name AttribVAlue

---------- -----------

ABCD 50405

shivu.1611-854900 SSC Rookie Points: 43 More actions · Answer 2

Thanks a lot 🙂 it worked. Please let me know how do we identify that we need to use ".[1]" while retrieving the 50405

Again Thanks a lot for your help

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

The "." is similar to DOS-style directory commands: it means the "root" of the current element.

The "[1]" means the first occurrence of that particular path within your context (seems a bit redundant in this scenario, but the .value() method requires a singleton element to be returned). Since elements could repeat, you will want to explicitly pick which one you want.

Maybe a more complicated example will help explain a bit

DECLARE @DocHandle int

DECLARE @XmlDocument XML

SET @XmlDocument = '<Things><Thing id="1">

<Attributes>

<Attribute name="Type">Widget</Attribute> --note: the"Attribute" element repeats for each Thing

<Attribute name="Model">1234</Attribute>

<Attribute name="color">blue</Attribute>

<Attribute name="color">red</Attribute>

</Attributes>

</Thing>

<Thing id="2">

<Attributes>

<Attribute name="Type">Doohickey</Attribute>

<Attribute name="Model">9987</Attribute>

<Attribute name="color">pink</Attribute>

<Attribute name="color">purple</Attribute>

</Attributes>

</Thing>

</Things>'

Select n.value('@id','int') id,

n.value('(./Attributes/Attribute[@name="Type"])[1]','varchar(100)') thingname,

n.value('(./Attributes/Attribute[@name="color"])[1]','varchar(100)') firstcolor,

n.value('(./Attributes/Attribute[@name="color"])[2]','varchar(100)') secondcolor

from @xmldocument.nodes('/Things/Thing') x(n)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

shivu.1611-854900 SSC Rookie Points: 43 More actions · Answer 4

shivu.1611-854900

SSC Rookie

Points: 43

August 6, 2014 at 9:52 am

#1735707

Thanks a lot 🙂

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 5

shivu.1611-854900 (8/6/2014)
Thanks a lot 🙂

No worries, Matt did a very good job explaining the singularity required. Suggest that you look into the overhead of OPENXML, don't' use if it can be avoided.

😎