Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query XML record

Author  Topic 

s72
Starting Member

1 Post

Posted - 2012-11-16 : 04:56:19
Hi,
First my name is Marian and I am from Romania.
I have a question, for me is complicated
I dont have any experience in xml/sql and I need some help to complete my project
I have a table named mytable with 3 columns
ID identity record
STATE XML record
STATUS boolean

And I have inserted in mytable 2 records
ID | STATE | STATUS |
=====================
1 | <?xml version="1.0" encoding="ISO-8859-1"?> <ADD="False" SET="False" SN="123456"/> | true
2 | <?xml version="1.0" encoding="ISO-8859-1"?> <ADD="False" SET="False" SN="123457"/> | false

My question is :
How can I create a SELECT statment to return SN value from STATE record WHERE ID = 1 for example.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 06:48:26
The XML does not appear to be well-formed, so you cannot use SQL's XML features to query this data.

What kind of project is it? Is it an academic project? Consult the professor or person who gave you the XML data and ask them for well-formed XML.

If they are unable to provide you with well-formed XML, you should use string functions in SQL to parse it and get the data you need.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-16 : 06:57:31
You have stated:
quote:

I have a table named mytable with 3 columns
ID identity record
STATE XML record
STATUS boolean

And I have inserted in mytable 2 records
ID | STATE | STATUS |
=====================
1 | <?xml version="1.0" encoding="ISO-8859-1"?> <ADD="False" SET="False" SN="123456"/> | true
2 | <?xml version="1.0" encoding="ISO-8859-1"?> <ADD="False" SET="False" SN="123457"/> | false


I would like to see the insert statement and the table create statement because for me it wasn't possible to insert that example data into an xml column...


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -