August 3, 2011 at 9:56 am
Hello,
My question is how can I query based on the attribute name “FirstAttribute” rather than using the position [1] ?? I’ve tried reading the syntax and cannot seem to get it to work. Thank you for any tips or advice.
SAMPLE XML (I have an attachment too):
<Root>
<Person>
<ID>101</ID>
<Name>MyName</Name>
<Data>
<item name = “FirstAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “SecondAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “ThirdAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “FourthAttributeName” valuestate = “1”>MyAttributeValue</item>
</Data>
</Person>
<Person>
<ID>102</ID>
<Name>MyName</Name>
<Data>
<item name = “I_Added_One_More” valuestate = “1”>MyAttributeValue</item>
<item name = “FirstAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “SecondAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “ThirdAttributeName” valuestate = “1”>MyAttributeValue</item>
<item name = “FourthAttributeName” valuestate = “1”>MyAttributeValue</item>
</Data>
</Person>
</Root>
SAMPLE SELECT CLAUSE: (If the list is always the same this works for me based upon position value)
per.value('(Data/item/text())[1]', 'varchar(50)') as FirstAttribute,
per.value('(Data/item/text())[2]', 'varchar(50)') as SecondAttribute
My question is how can I query based on the attribute name “FirstAttribute” rather than using the position [1] ?? I’ve tried reading the syntax and cannot seem to get it to work.
HERE IS THE WHOLE QUERY I WROTE:
SELECT
per.value('(ID/text())[1]', ‘int') as ID,
per.value('(Name/text())[1]', 'varchar(50)') as Name,
per.value('(Data/item/text())[1]', 'varchar(50)') as FirstAttribute,
per.value('(Data/item/text())[2]', 'varchar(50)') as SecondAttribute
FROM
dbo.MyXMLTable
CROSS APPLY
xml_data.nodes('/Root/Person') AS Person(per)
August 3, 2011 at 11:42 am
I assume that you actually want where the name attribute is "FirstAttributeName" since there is nothing named "FirstAttribute" in your sample.
This should give you what you want.
SELECT per.value('(Data/item[@name="FirstAttributeName"])[1]', 'varchar(50)') AS FirstAttribute
You will still need to use the [1] since the value() function requires a singleton.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2011 at 12:54 pm
That was exactly the example I was looking for THANK YOU! I was having a hard time finding an example that shows how to reference the Attribute Name.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply