Reading a column of type XML

Question

Reading a column of type XML

snelliga

SSC-Addicted

Points: 467
More actions
April 5, 2010 at 7:13 am

#143905

We are starting to explore storing XML in our SQL 2005 database(s).
I have a table with a row defined as XML and cannot figure out how to query the table returning the XML row as formatted data. i.e. Data returned in columns.
Is this possible in SQL 2005? 2008? etc.
If so could I get a sample query to accomplish this?
Following is an example of the XML column.
Any help or direction would be Appreciated
-Steve
<FsKonaKartOrderHeader>
<orderNumber>1</orderNumber>
<approver />
<paymentType>1</paymentType>
<paymentLevel>HEADER</paymentLevel>
<lineItems>
<FsKonaKartLineItem>
<quantity>2</quantity>
<sku>28811550</sku>
<description>PAPER TOWEL ROLL #26301 NATURAL (28811550)</description>
<price>21.2700</price>
<lineNumberId>1</lineNumberId>
<orderNumberId>1</orderNumberId>
<warehouse>SR</warehouse>
<accountString>0110008-141</accountString>
</FsKonaKartLineItem>
<FsKonaKartLineItem>
<quantity>2</quantity>
<sku>28811700</sku>
<description>PAPER TOWEL #21924 ACCLAIM NATURAL CFOLD (28811700)</description>
<price>16.9500</price>
<lineNumberId>2</lineNumberId>
<orderNumberId>1</orderNumberId>
<warehouse>SR</warehouse>
<accountString>0110008-141</accountString>
</FsKonaKartLineItem>
</lineItems>
<customer>
<netid>abc123</netid>
<badge>9999</badge>
</customer>
<shipTo>
<shipToName>Customer1</shipToName>
<shipToDepartment />
<shipToStreetAddress1>200 Main Street</shipToStreetAddress1>
<shipToStreetAddress2 />
<shipToCity>ANYCITY</shipToCity>
<shipToState>ST</shipToState>
<shipToZip>99999</shipToZip>
</shipTo>
<orderTotal>76.44</orderTotal>
<needByDate>2009-10-07</needByDate>
<comment>1st Floor North Men's Restroom</comment>
<createdBy>user1</createdBy>
</FsKonaKartOrderHeader>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You'll need to look up the "query", "nodes", and "value" functions in Books Online. Those are the starting point for using SQL 2005/2008 XQuery.

Those allow you to query XML, turn it into flat tables, etc.

Check them out, then let us know what questions you have on them. They're a bit of a pain to learn, but very useful once you've used them a bit.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 2

Is this what you need? Otherwise, please post the desired output.

declare @var xml

set @var = '<AttributeCollection>

<Attribute>

<value>Personal P and C</value>

<attributeid>100055</attributeid>

</Attribute>

<Attribute>

<value>Commercial P and C</value>

<attributeid>100056</attributeid>

</Attribute>

<Attribute>

<value>Life</value>

<attributeid>100057</attributeid>

</Attribute>

<Attribute>

<value>Health</value>

<attributeid>100058</attributeid>

</Attribute>

</AttributeCollection>'

select

c.value('(value)[1]', 'varchar(50)'),

c.value('(attributeid)[1]', 'varchar(50)')

from

@var.nodes('//Attribute')tab(c)

- arjun

https://sqlroadie.com/