Select In Xml Field
-
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 ?
-
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);
GOINSERT 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>');
GOSELECT P.N.value('.','int') AS NumberSerializedLabels
FROM #XML X
CROSS APPLY X.XMLField.nodes('PrintSetup/NumberSerializedLabels') P(N);
GODROP TABLE #XML;
GOThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Piling on, can you post a proper example of the XML data please, the data you've posted is ill-formed.
😎 -
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. 🙂 - ivor1994 - Monday, June 26, 2017 1:32 AM
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
GONotice 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.
Larnu.uk -
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).
- ivor1994 - Monday, June 26, 2017 3:31 AMThorn 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.
Larnu.uk -
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.
😎 - Eirikur Eiriksson - Monday, June 26, 2017 4:34 AM
Nice spot Eirikur, hadn't even thought about that. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
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.
- ivor1994 - Monday, June 26, 2017 5:21 AM
This should get you passed this hurdle
😎USE TEEST;
GO
SET NOCOUNT ON;DECLARE @NVXML TABLE (XID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, NVXMLField NVARCHAR(MAX) NOT NULL);
DECLARE @NXSTR NVARCHAR(MAX) = N'<?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>';INSERT INTO @NVXML(NVXMLField) VALUES
(@NXSTR)
,(@NXSTR)
,(@NXSTR)
,(@NXSTR)
,(@NXSTR)
,(@NXSTR)
;
;WITH BASE_DATA AS
(
SELECT
NX.XID
,CONVERT(XML,CONVERT(VARCHAR(MAX),NX.NVXMLField,0),1) AS CXML
FROM @NVXML NX
)
SELECT
BD.XID
,P.N.value('.','int') AS NumberSerializedLabels
FROM BASE_DATA BD
CROSS APPLY BD.CXML.nodes('PrintSetup/NumberSerializedLabels') P(N); - Eirikur Eiriksson - Monday, June 26, 2017 6:08 AM
I think this solution will probably work properly so i will mark this as answer.
I have only simple knowledge about XML so i will wait, learn more and than i will analyze this structure because for now it's black magic for me :D. Also it's too risky for use it in the large company database. Especially i'm not a SQL master. I'm using SQL only for read something and simple change.
Thank you guys for your time.
Eirikur Eiriksson. I think you solution will be useful for me in the future :). -
Thank you for the feedback and if you have any further questions on XML then don't hesitate coming back here 😉
😎Quick thought, you should use the text() function, avoids rather expensive reconstruction within the XML operation, here is an example
;WITH BASE_DATA AS
(
SELECT
NX.XID
,CONVERT(XML,CONVERT(VARCHAR(MAX),NX.NVXMLField,0),1) AS CXML
FROM @NVXML NX
)
SELECT
BD.XID
,P.N.value('(./text())[1]','int') AS NumberSerializedLabels
FROM BASE_DATA BD
CROSS APPLY BD.CXML.nodes('PrintSetup/NumberSerializedLabels') P(N); - ivor1994 - Monday, June 26, 2017 7:09 AM
Storing XML as character data is a true SQL Server anti-pattern and should be avoided if possible, there are few penalties using that kind of methods and no benefits.
😎
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply