how to use node() to select data from this xml-CodePudding

i have read some articles on Internet regrading node() method to extract data from xml.

i have this xml:

    <root><startsite personID="1" SaleID="2" regionID="3"/> </root>

i want to get personID, SaleID,region ID from the xml. What i tried is:

SELECT T.c.query('.') AS result  
FROM   @x.nodes('/Root/startsite ') T(c)

but it shows me Error. Can you please help?

CodePudding user response：

You should fix the xml as mentioned and use the "value" method to extract the data.

DECLARE @x XML   
SET @x='<root><startsite personID="1" SaleID="2" regionID="3"/></root>'  

SELECT c.value('@personID', 'Int') As personID,
       c.value('@SaleID', 'Int') As SaleID,
       c.value('@regionID', 'Int') As regionID
FROM @x.nodes('/root/startsite') T(c)

CodePudding user response：

the basic syntax is given below

nodes (XQuery) as Table(Column)

The query then returns the context node from each row

DECLARE @x XML   
SET @x='<Root>  
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>  
    <row id="2"><name>moe</name></row>  
    <row id="3" />  
</Root>'  
SELECT T.c.query('.') AS result  
FROM   @x.nodes('/Root/row') T(c)  
GO