XML data type and Sql server 2005

Question

i have one table where one field type is xml and there data is saved in xml format. my xml is

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <DELETED>
    <STOCK_CODE>111111</STOCK_CODE>
    <MakeID>GB00000001</MakeID>
    <ModelID>GB00000001</ModelID>
    <EngineSize />
    <YearMakeFrom>0</YearMakeFrom>
    <YearMakeTo>0</YearMakeTo>
    <Automatic>1</Automatic>
    <SemiAutomatic>1</SemiAutomatic>
    <Manual>0</Manual>
    <OtherInfo />
    <Status>UPDATED</Status>
  </DELETED>
</Record>

so please tell me how could i query the above xml document in sql server 2005. please help. thanks.

What do you want to query from that XML document?? What do you want to extract from it?? — marc_s
– marc_s, Commented Jan 18, 2011 at 13:18

marc_s · Accepted Answer · 2011-01-18 14:39:17Z

1

You're not saying what you're looking for exactly - so here's just a guess.

Assume you have a table full of rows, each row has a XML column XmlData which contains the above structure, and you want to get the Stock_Code and ModelID from that XML.

In that case, you'd use something like this:

SELECT 
    ID,
    XmlData.value('(/Record/DELETED/STOCK_CODE)[1]', 'BIGINT') AS 'StockCode',
    XmlData.value('(/Record/DELETED/ModelID)[1]', 'VARCHAR(25)') AS 'ModelID' 
FROM 
    dbo.YourTable
WHERE
    (some condition)

Is that what you're looking for?? If not: please clarify your question!

answered Jan 18, 2011 at 14:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XML data type and Sql server 2005

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related