Load specific element from XML

Question

I have the following xml:

<root>
 <123 type="line">
  <aa type="item">
   <value>1</value>
  </aa>
 </123>
 <234 type="line">
  <bb type="item">
   <value>2</value>
  </bb>
 </234>
</root>

How can I from bulk loading the xml into a database table (field xmldata) query the xml so I get all values under type="line" and then all values under type="item" so I can then get to the value attribute. I can't hard code the attribute (eg 123) because it changes all the time, but the type value will remain constant.

Answer 1

You should use the nodes() method of the XML data type to shred your XML.

If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you are using but that the node names are valid XML names.

declare @XML xml = '
<root>
  <A123 type="line">
    <aa type="item">
      <value>1</value>
    </aa>
  </A123>
  <B234 type="line">
    <bb type="item">
      <value>2</value>
    </bb>
  </B234>
</root>';

select T.X.value('text()[1]', 'int') as Value
from @XML.nodes('/root/*[@type = "line"]/*[@type = "item"]/value') as T(X)

A breakdown of the xPath in the nodes function:

From /root give me all nodes /root/* where the attribute type is line /root/*[@type = "line"] and from there give me all nodes /root/*[@type = "line"]* where the attribute type is item /root/*[@type = "line"]/*[@type = "item"].

Finally get the value node /root/*[@type = "line"]/*[@type = "item"]/value and extract the text node using the value() function.

Answer 2

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

Answer 3

As i understand you need the XML upload option which customized but not hard coded one you need.

SELECT * FROM T UPDATE T SET XmlCol =(
SELECT * FROM OPENROWSET(
   BULK 'C:\SampleFolder\SampleData3.txt',
           SINGLE_BLOB
) AS x
)
WHERE IntCol = 1;
GO

The answer is available in MSDN, Please refer the below link MSDN Article