Is there any way the XML can be better formed (I understand this isn't always possible)? I've never come across dynamic element names before, and while that certainly doesn't mean it can't be done, it's much more difficult. However, if you're able to get some static items in your XML, it makes it much easier to deal with. If the XML was stored in a similar fashion to this:
<root>
<line id="123">
<item>
<value>1</value>
</item>
</line>
</root>
(edit: how is the XML being generated in the first place? I've never seen an instance where the name of the attribute itself is dynamic, rather than having a static name with a dynamic value)
Then using sp_xml_preparedocument and OPENXML would do what you want:
sp_xml_preparedocument: https://msdn.microsoft.com/en-us/library/ms187367.aspx
OPENXML: https://msdn.microsoft.com/en-us/library/ms186918.aspx
declare @xmlstring xml
declare @xmlptr int
set @xmlstring = '' -- insert your XML here to test
-- create a pointer to a new xml document using your xml data
exec sp_xml_preparedocument @xmptr output, @xmlstring
insert into TableName
(
Col1 varchar(50)
,Col2 varchar(50)
,Col3 int
,Col4 datetime
)
-- You can then select from it like a table
select
Col1
,Col2
,Col3
,Col4
from
-- having clearly defined paths through the XML makes things a lot easier
openxml (@xmlptr, 'XmlRoot/XmlNode', 1)
with
-- define the data types and column names for values coming from the xml
-- so they match the destination table (makes it easier to read)
(
Col1 varchar(50) 'Col1'
,Col2 varchar(50) 'Col2'
,Col3 int 'Col3'
,Col4 datetime 'Col4'
)
-- release the document to free up memory
exec sp_xml_removedocument @xmlptr
123
and234
are invalid XML element names so this xml is not well-formed and cannot be parsed. You mention value attribute in your question but I see only value elements in your sample document. Please provide a well formed sample document and expected results. – Dan Guzman May 18 '15 at 11:51