How to query xml fileds inside the column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to query xml fileds inside the column?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-09 : 08:39:09

Hi

I have a veiw (ViewOrd) in sql 2005 (standard edition). One of the colum has xml tag as listed below

<?xml version="1.0"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AddressId>83f7e4e1-2b32-44a4-af7b-039459e0bef5</AddressId>
<UserName>mike</UserName>
<FirstName>mike</FirstName>
<LastName>smith</LastName>
<Phone>0123456789</Phone>
<Email>some@somedomain.co.uk</Email>
<Address1>111</Address1>
<Address2>firstlane</Address2>
<City>firstcity</City>
<StateOrRegion>London</StateOrRegion>
<PostalCode>R56 3HT</PostalCode>
<Country>GB</Country>
<AddressType>BillingAddress</AddressType>
<LastSaved>2008-08-09T11:37:17.7627301Z</LastSaved>
</Address>

How do I read (query) the address from the above xml tag?

Advance Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 11:37:37

You want to get the whole node or just data from items?

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-09 : 12:57:39

quote:
Originally posted by visakh16

You want to get the whole node or just data from items?

Hi Thanks for the reply

Just data from items

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 13:10:47

Are you using sql 2005?

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-09 : 16:29:05

quote:
Originally posted by visakh16

Are you using sql 2005?

Hi
Yes I am using sql 2005 standard edition

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 02:24:44

then make use of XML xpath expressions. something like

SELECT ad.addval.value(./AddressId,'int'),
ad.addval.value(./UserName,'varchar(50)'),
ad.addval.value(./FirstName,'varchar(50)'),
ad.addval.value(./LastName,'varchar(50)'),
ad.addval.value(./Phone,'varchar(50)')
....
FROM yourxmlcolumn.nodes('/xml/address') as ad(addval)

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-10 : 18:16:07

Hi

Its not working. Also the xml element stored in a column which datatype is nvarchar(1500).

quote:

Originally posted by visakh16

then make use of XML xpath expressions. something like

SELECT ad.addval.value(./AddressId,'int'),
ad.addval.value(./UserName,'varchar(50)'),
ad.addval.value(./FirstName,'varchar(50)'),
ad.addval.value(./LastName,'varchar(50)'),
ad.addval.value(./Phone,'varchar(50)')
....
FROM yourxmlcolumn.nodes('/xml/address') as ad(addval)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 23:56:17

quote:
Originally posted by Vaishu

Hi

Its not working. Also the xml element stored in a column which datatype is nvarchar(1500).

quote:
Originally posted by visakh16

then make use of XML xpath expressions. something like

SELECT ad.addval.value(./AddressId,'int'),
ad.addval.value(./UserName,'varchar(50)'),
ad.addval.value(./FirstName,'varchar(50)'),
ad.addval.value(./LastName,'varchar(50)'),
ad.addval.value(./Phone,'varchar(50)')
....
FROM yourxmlcolumn.nodes('/xml/address') as ad(addval)

the datatype should be xml for the above method to work. if you field is varchar then use OPENXML method.

http://msdn.microsoft.com/en-us/library/ms178653.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 03:15:06

quote:
Originally posted by visakh16

quote:
Originally posted by Vaishu

Hi

Its not working. Also the xml element stored in a column which datatype is nvarchar(1500).

quote:
Originally posted by visakh16

then make use of XML xpath expressions. something like

SELECT ad.addval.value(./AddressId,'int'),
ad.addval.value(./UserName,'varchar(50)'),
ad.addval.value(./FirstName,'varchar(50)'),
ad.addval.value(./LastName,'varchar(50)'),
ad.addval.value(./Phone,'varchar(50)')
....
FROM yourxmlcolumn.nodes('/xml/address') as ad(addval)

the datatype should be xml for the above method to work. if you field is varchar then use OPENXML method.

http://msdn.microsoft.com/en-us/library/ms178653.aspx

That should be http://msdn.microsoft.com/en-us/library/aa276847.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 03:39:51

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by Vaishu

Hi

Its not working. Also the xml element stored in a column which datatype is nvarchar(1500).

quote:
Originally posted by visakh16

then make use of XML xpath expressions. something like

SELECT ad.addval.value(./AddressId,'int'),
ad.addval.value(./UserName,'varchar(50)'),
ad.addval.value(./FirstName,'varchar(50)'),
ad.addval.value(./LastName,'varchar(50)'),
ad.addval.value(./Phone,'varchar(50)')
....
FROM yourxmlcolumn.nodes('/xml/address') as ad(addval)

the datatype should be xml for the above method to work. if you field is varchar then use OPENXML method.

http://msdn.microsoft.com/en-us/library/ms178653.aspx

That should be http://msdn.microsoft.com/en-us/library/aa276847.aspx

Madhivanan

Failing to plan is Planning to fail

another copy paste mistake

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-11 : 04:01:00

<<
another copy paste mistake
>>

I already guessed it

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 04:02:56

quote:
Originally posted by madhivanan

<<
another copy paste mistake
>>

I already guessed it

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-11 : 06:08:22

Hi

Thanks a lot people. The below code works

quote:
DECLARE @idoc int
DECLARE @doc nvarchar(1500)
SET @doc = (select BillToAddress from tbl_Order where CreatedBy = 'someone')

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/Address',2)
WITH (UserName nvarchar(50),
FirstName nvarchar(50),
LastName nvarchar(50),
Phone nvarchar(50),
Email nvarchar(50),
Address1 nvarchar(150),
Address2 nvarchar (150),
City nvarchar (50),
StateOrRegion nvarchar(150),
PostalCode nvarchar(50),
Country nvarchar(50),
AddressType nvarchar(50))

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-11 : 06:38:39

Hi
The above code(my previous thread) query the address from one column of the table, how do I select other columns of the table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 06:44:46

quote:
Originally posted by Vaishu

Hi
The above code(my previous thread) query the address from one column of the table, how do I select other columns of the table.

You need it loop through your records and apply the OPENXML on each records xml column. the structure will be like this

DECLARE @PK int,@xmlcol varchar(8000)
SELECT @PK=MIN(PKcol)
FROm YourTable

WHILE @PK IS NOT NULL
BEGIN
SELECT @xmlcol=yourxmlcol
FROm YourTable
WHERE PKcol=@PK

...your OPENXML code

SELECT @PK=MIN(PKcol)
FROm YourTable
WHERE PKcol>@PK
END

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-11 : 07:53:24

Hi

Sorry to pestering you. When I try the below code I am getting this error. Also the column of the table where the xml data exist is nvarchar(1500).

Conversion failed when converting the nvarchar value '<?xml version="1.0"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<AddressId>390dc843-52c6-442d-acad-15290c22be86</AddressId>
<UserName>mike</UserName>
<FirstName>mike</FirstName>
......
.....
<LastName>smith</LastName>
<Country>GB</Country>
<AddressType>BillingAddress</AddressType>
<LastSaved>2008-08-09T11:07:55.4875891Z</LastSaved>
</Address>' to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

quote:
Originally posted by visakh16

quote:
Originally posted by Vaishu

Hi
The above code(my previous thread) query the address from one column of the table, how do I select other columns of the table.

You need it loop through your records and apply the OPENXML on each records xml column. the structure will be like this

DECLARE @PK int,@xmlcol varchar(8000)
SELECT @PK=MIN(PKcol)
FROm YourTable

WHILE @PK IS NOT NULL
BEGIN
SELECT @xmlcol=yourxmlcol
FROm YourTable
WHERE PKcol=@PK

...your OPENXML code

SELECT @PK=MIN(PKcol)
FROm YourTable
WHERE PKcol>@PK
END

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-08-12 : 05:27:05

HI Visak

Thanks a lot your code works.

quote:
Originally posted by visakh16

quote:
Originally posted by Vaishu

Hi
The above code(my previous thread) query the address from one column of the table, how do I select other columns of the table.

You need it loop through your records and apply the OPENXML on each records xml column. the structure will be like this

DECLARE @PK int,@xmlcol varchar(8000)
SELECT @PK=MIN(PKcol)
FROm YourTable

WHILE @PK IS NOT NULL
BEGIN
SELECT @xmlcol=yourxmlcol
FROm YourTable
WHERE PKcol=@PK

...your OPENXML code

SELECT @PK=MIN(PKcol)
FROm YourTable
WHERE PKcol>@PK
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources