CODEWITHSUNDEEP
sqlxmlsql-server-2005
Thomas
Thomas

Reputation: 34188

XML data type and Sql server 2005

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.

Upvotes: 0

Views: 172

Answers (1)

marc_s
marc_s

Reputation: 754278

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!

Upvotes: 1

Related Questions