March 27, 2009 at 8:18 am
Hi,
We have a table that is containing the XML file data(whole xml file data).
We have a requirement to pull the Tag name and Tag Value from the table data and insert into the other table.
The new table format like:
ID Varchar(100)
ComputerIDVarchar(100)
FieldNameVarchar(100)
FieldValueVarchar(100)
I would be greateful if any one of you could please let me know how i can pull the xml tag and value details from the table data and insert into the other table by using the sql stored procedure?
Please find the XML data file attached.
Many Thanks,
Madhu
March 27, 2009 at 9:55 am
Does this help?
declare @XML XML;
select @XML = ' ';
select x.y.value('local-name(.)', 'VARCHAR(50)'),
x.y.value('.', 'VARCHAR(50)')
from @XML.nodes('//*') x(y);
That's sample XML, but it pulls name-value pairs from it, and that sounds like what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 10:10 am
GSquared (3/27/2009)
Does this help?
declare @XML XML;
select @XML = ' ';
select x.y.value('local-name(.)', 'VARCHAR(50)'),
x.y.value('.', 'VARCHAR(50)')
from @XML.nodes('//*') x(y);
That's sample XML, but it pulls name-value pairs from it, and that sounds like what you need.
If you want attribute values as well, do this
select r.value('local-name(.)','varchar(100)') as ElementName,
r.value('text()[1]','varchar(100)') as ElementValue,
a.value('local-name(.)','varchar(100)') as AttributeName,
a.value('.','varchar(100)') as AttributeValue
from @XML.nodes('//*') as x(r)
outer apply r.nodes('@*') as y(a)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537January 5, 2015 at 12:27 pm
Hi Guys,
Please help me out!! AsAp...
I have a unstructured XML files …. Means … each file can have different no of NODES…
So please let me know how I can upload this file if I do not know what fields are coming in this file.
I need query to upload unstructured XMl file in sql server2008
Thanks
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy