SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select In Xml Field


Select In Xml Field

Author
Message
ivor1994
ivor1994
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 16
Hello,
I have problem with select. I need to have a value of NumberSerializedLabels in this xml structure in another column. This xml structure is in field in the database.
Something Like:
Value
108
...

<?xml version="1.0" encoding="utf-8"?>
<XMLScript Version="2.0">
<Command Name="xxxxxxxxxxxxxxxxxxxxxxxxxx">
<Print WaitForJobToComplete="false" Timeout="2000">
<Format>D:\xxx\xxxxxx\xxxx\xxxxx</Format>
<PrintSetup>
<NumberSerializedLabels>108</NumberSerializedLabels>
<Printer>XXX_XXX_XX</Printer>
</PrintSetup>

I make something like this but it's didn't work.
select top 1
cast(TemplateData as xml).value('(//xmlscript//command//print//format/printsetup/@numberserializedlabels)[1]', 'nvarchar(max)') as Value
FROM [dbo].[xxxx]

Someone Can help me ?
Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48271 Visits: 16141
My guess would be that the error you got was "XML parsing: line 9, character 13, unexpected end of input"? This is because your xml is malformed.

Your xml should look like this:
<?xml version="1.0" encoding="utf-8"?>
<XMLScript Version="2.0" />
<Command Name="xxxxxxxxxxxxxxxxxxxxxxxxxx" />
<Print WaitForJobToComplete="false" Timeout="2000" />
<Format>D:\xxx\xxxxxx\xxxx\xxxxx</Format>
<PrintSetup>
<NumberSerializedLabels>108</NumberSerializedLabels>
<Printer>XXX_XXX_XX</Printer>
</PrintSetup>

Note the extra /'s at the end of Command and Print.

You'll need tpo fix your XML first before you can query it using XQuery. Then you can actually store the xml as an xml datatype and query it directly.

For example:
CREATE TABLE #XML (XMLField xml);
GO

INSERT INTO #XML
VALUES('<?xml version="1.0" encoding="utf-8"?>
<XMLScript Version="2.0" />
<Command Name="xxxxxxxxxxxxxxxxxxxxxxxxxx" />
<Print WaitForJobToComplete="false" Timeout="2000" />
<Format>D:\xxx\xxxxxx\xxxx\xxxxx</Format>
<PrintSetup>
<NumberSerializedLabels>108</NumberSerializedLabels>
<Printer>XXX_XXX_XX</Printer>
</PrintSetup>');
GO

SELECT P.N.value('.','int') AS NumberSerializedLabels
FROM #XML X
CROSS APPLY X.XMLField.nodes('PrintSetup/NumberSerializedLabels') P(N);
GO

DROP TABLE #XML;
GO



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100314 Visits: 20907
Piling on, can you post a proper example of the XML data please, the data you've posted is ill-formed.
Cool
ivor1994
ivor1994
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 16
Thorn A Thank you for your time but i need to do it for over a 1000 fields in the column TemplateData and make from it another column so it's no sense to create a new table for each one.
In my select i have error like this:
XML parsing: line 1, character 38, unable to switch the encoding
There is any other solution for my problem ?
I will be very grateful for any useful answers. Smile
Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48271 Visits: 16141
ivor1994 - Monday, June 26, 2017 1:32 AM
Thorn A Thank you for your time but i need to do it for over a 1000 fields in the column TemplateData and make from it another column so it's no sense to create a new table for each one.
In my select i have error like this:
XML parsing: line 1, character 38, unable to switch the encoding
There is any other solution for my problem ?
I will be very grateful for any useful answers. Smile


Are you saying that you can't fix the xml? If so, this is a problem, If you're xml is malformed you can't query it with xQuery. This means you'd have to do something "silly" like use a giant string

What is your actual goal here? Your original post said that you wanted to get the value of NumberSerializedLabels, however, now you say you need to get the value of 1,000 fields.

You could change your query to do this instead, and then bring back your XML's values. This doesn't have a join but instead takes the first value of that data type. if you have any second/third entries of a node you'll need to change [1] to [2] or so on:
SELECT X.XMLField.value('(Command/@Name)[1]','varchar(20)') AS CommandName,
X.XMLField.value('(PrintSetup/NumberSerializedLabels)[1]','int') AS NumberSerializedLabels,
X.XMLField.value('(PrintSetup/Printer)[1]','varchar(20)') AS Printer
FROM #XML X
GO


Notice that returning the value for Name property for Command is different to the other fields.

Again, this isn't going to work if your XML is malformed and or if it's not stored in an xml column (you can't xquery a (n)varchar). If it is still malformed, you need to fix it, both in your data, and whatever is producing it(so that you don't have this problem going forwards), and then store it as xml. If you can't fix it, you're going to in for a world of hurt and probably will need to attempt some kind of string manipulation query. That's going to be slow and tedious.

A lot of users here will sing this song, but if you have data that represents a certain type of data, store it is that data. If you have number data, store it in a int or decimal column. If it's a date (only), then date. If you need the time as well then store in datetime/datetime2. Thus xml records go in an xml column, not a (n)varchar, as SQL Server has that data type. Using an XML column type would have flagged the malformed xml to you very early on, so you then wouldn't be attempting to fix it.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
ivor1994
ivor1994
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 16
Thorn A again thank you. I'm make a mistake because i have a 1000 rows not fields so you probably don't understand me.
Also you probably think this is the Xml file but it's a communication XML between BarTender and web service application to print. Somehow Bartender is able to read the data from this XML without formatting it.
To clear all the misunderstandings about my problem. I have column like in the image. I need to read from each row the value of NumberSerializedLabels. I try you select but like you said i have error Cannot call methods on nvarchar(max).

Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48271 Visits: 16141
ivor1994 - Monday, June 26, 2017 3:31 AM
Thorn A again thank you. I'm make a mistake because i have a 1000 rows not fields so you probably don't understand me.
Also you probably think this is the Xml file but it's a communication XML between BarTender and web service application to print. Somehow Bartender is able to read the data from this XML without formatting it.
To clear all the misunderstandings about my problem. I have column like in the image. I need to read from each row the value of NumberSerializedLabels. I try you select but like you said i have error Cannot call methods on nvarchar(max).

As I said, you're storing your xml as an nvarchar(max) and you can't use XQuery on a nvarchar field. The problem is, as well, that your xml is malformed, so you can't convert it to xml.

Your third party application might be able to handle the malformed xml, but SQL Server will not. I can't stress enough, you need to fix your xml first.



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100314 Visits: 20907
Since the encoding in the header is marked as utf-8, you will have to convert the nvarchar to varchar before converting to XML, SQL Server cannot switch the encoding implicitly.
Cool
Thom A
Thom A
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48271 Visits: 16141
Eirikur Eiriksson - Monday, June 26, 2017 4:34 AM
Since the encoding in the header is marked as utf-8, you will have to convert the nvarchar to varchar before converting to XML, SQL Server cannot switch the encoding implicitly.
Cool
Nice spot Eirikur, hadn't even thought about that. Smile



Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
ivor1994
ivor1994
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 16
Thank you very much Thorn A. Thanks to you i understand everything and I know that I have a very complicated problem. I will try to convert the file only for learn something because for more than few rows this method is useless. I will search the solution in different program than SQL.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search