How to read value from xml attribute value from xml?

Question

question

ramesh.duppati asked Dec 14, '12

How to read value from xml attribute value from xml?

Hi All I need your help for my below 2 queries. Please reply to me asap. Thanks.. ![alt text][1] [1]: /storage/temp/528-aa.jpeg

sql xml sqlxml

aa.jpeg (38.0 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Valentino Vranken commented · Dec 14, 2012 at 06:46 AM

Please post text instead of that image, we can't copy/paste from it...

0 ·

Answer 1 · 2012-12-14T16:22:39Z

mjharper answered Dec 14, '12

no problem - this should do the job... IF OBJECT_ID('tempdb..#demoData') IS NOT NULL DROP TABLE #demoData CREATE TABLE #demoData ( MyXmlData XML ) INSERT INTO #demoData ( MyXmlData ) VALUES ( '' );--insert xml data in here WITH XMLNAMESPACES (' http://xxx.com/' AS ns1) SELECT N.C.value('(@ColorId)[1]', 'varchar(20)') AS 'Color_id', N.C.value('ns1:font[1]', 'varchar(20)') AS font, N.C.value('ns1:style[1]', 'varchar(20)') AS style FROM #demoData AS dd CROSS APPLY MyXmlData.nodes('/ns1:Colors/ns1:Color') N(C)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2012-12-14T09:11:57Z

mjharper answered Dec 14, '12

Hi For the first approach this query will work (you'll need to finish the preceding SQL statement with a semicolon): WITH XMLNAMESPACES (' http://xxx.com' AS ns1) SELECT a.b.value('(@ColorId)[1]', 'varchar(20)') AS 'Color_id' FROM @MyXML.nodes('/ns1:Colors/ns1:Color') a(b) For the second approach you can use a similar query: CREATE TABLE #demoData ( MyXmlData XML ) INSERT INTO #demoData ( MyXmlData ) VALUES ( '' ); --insert your xml here WITH XMLNAMESPACES (' http://xxx.com' AS ns1) SELECT N.C.value('(@ColorId)[1]', 'varchar(20)') AS 'Color_id' FROM #demoData AS dd CROSS APPLY MyXmlData.nodes('/ns1:Colors/ns1:Color') N(C)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2012-12-14T12:46:46Z

ramesh.duppati answered Dec 14, '12

Hi Harper, Really thanks for you reply and it is working for me. Also I would like to know for approach 2 that how to get directly colorid from XML database column. By using second approach my work will be easier.. HI Vranken, I try to copy code in text format, but I can 't see everything in post preview. sorry for that.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper commented · Dec 14, 2012 at 02:07 PM

Hi - I have update my original answer to include both approaches.

0 ·

Answer 4 · 2012-12-14T14:22:20Z

ramesh.duppati answered Dec 14, '12

Really appreciate your help here Haper. it works for me..

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper commented · Dec 14, 2012 at 02:29 PM

Glad it works. If you can accept the answer then hopefully that helps future users solve the same or similar problem. Thanks.

0 ·

Answer 5 · 2012-12-14T15:23:57Z

ramesh.duppati answered Dec 14, '12

Hi Harper, Sorry to bug you again. But need one more small help here. In my above XML, for each Color tag I am having two more sub tags with information as bleow. <'Color colorid="aaaa" last_modified="date"> <'font>verdana<'/font> <'style>Italic<'/italic> <'/Color> I would like use 2nd approach to get data like below table format Column1 Column2 Column 3 aaaa verdana Italic bbb Calibri Bold

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 6 · 2012-12-14T16:46:25Z

ramesh.duppati answered Dec 14, '12

Oh man, You rocks really. It works like charm.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question